Countif function

[Mike]

If you have two conditions that must be met, you can
actually use the SUMPRODUCT formula:

=SUMPRODUCT(--(b2:b5>1),--(e2:e5>1))

This will give you the count of how many records where
the cell in B is greater than 1 AND the cell in E is
greater than 1.

Mike.

 

[Pingger]

Mike,

I'm following this thread and I have a similar application
except my data contains dates in MMDDYYYY format from Jan
12,2002 through Mar5,2003 and stored in AA5:BA5. In B5 I
need to count all dates in Jan2002, in C5 Feb2002...
through Mar 2003. Is there a way to do this using
SUMPRODUCT?

I've used "=SUMPRODUCT(--(MONTH(AA5:BA5)=8))" but it
ignores the year and count occurences together.

TIA

Pingger

 

[Mike]

Hi, Pingger.

Yes, Sumproduct can accomplish this. In cell B5, you
would need to enter this formula:

=SUMPRODUCT(--(MONTH($AA$5:$BA$5)=1),--(YEAR($AA$5:$BA$5)
=2002))

In Cell C5, you wold change the month to 2; and so on for
each month and year.

Mike.

 

[Pingger]

Ooohhhhhh! Works like a charm.

Thanks, Mike.

-----Original Message-----
Hi, Pingger.

Yes, Sumproduct can accomplish this. In cell B5, you
would need to enter this formula:

=SUMPRODUCT(--(MONTH($AA$5:$BA$5)=1),--(YEAR($AA$5:$BA$5)
=2002))

In Cell C5, you wold change the month to 2; and so on for
each month and year.

Mike.

.