Use multiple criteria with COUNTIF: between dates and not blank

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using Excel 2003 I need to count rows where the date in column A is in a
particular month and year and column G is not blank.
 
=SUMPRODUCT(--(G2:G20<>""),--(MONTH(A2:A20)=1))

for January

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
oops, mis-read it. I see that now seeing Ardus' response.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
I'm actually East of Colorado!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Many thanks guys for the very quick response. Worked perfectly. Sorry it
took me so long to reply, but I wasn't able to verify my e-mail address.
 
Hello,

I have a similiar problem but I need to count the number of counts in EVERY
bin of a graph seperately. i.e. How many have an (x,y) of (0,0) (0,1)(1,0)
and so on. For the numbers I am talking about I need to do this several
thousand times and copying the formula below doesn't work because it changes
the column it's looking at every time copy to a new cell. Is there another
way that can be easily copied?

Thanks,

Eliza
 
I have a similar issue: I have a range of dates that I'm trackun using the
COUNTIF function based on the dates that are 91-180, 181-270, & 271-365, days
old, based on the TODAY date. for the 90-180days, I'm using the following,
but doesn't pan out:
=COUNTIF(MASTER!AI5:AI5997,">="&TODAY()-180),
(MASTER!AI5:AI5997,"<="&TODAY()-90)

Any help would be graetly appreciated!!

Keith
 
Try this:

=SUMPRODUCT(--(MASTER!AI5:AI5997>=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91))
 
Thanks much...That works great.
My dates are color coded, so how can I use the same formula, but only count
the dates in a specific color? Can this even be done?

Best regards
 
See if this gives you an idea...

Your dates are color coded for a reason. Use that reason as the logic in a
formula. If they're color coded using conditional formatting, use the same
logic of the conditional formatting rule to write a formula.
 
Back
Top