Help in SUMPRODUCT formula...

[Astro]

Currently I'm encounting error for SUMPRODUCT formula:

Name Judgement Date
John 1 Jan-08
Sarah 1 Jan-08
John 1 Feb-08
John 0 Jan-08
John 1 Jan-08

What formula can I use so that the count will be 2 for name "John",
judgement "1", month "Jan" & year "2008"?

Please help.... Thank you.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100=1),--(MONTH(C2:C100)=1),
--(YEAR(C2:C100)=2008))

 

[Fred Smith]

It depends on whether you have dates or text in the third column.

If text:
=sumproduct(--(a2:a1000="John"),--(b2:b1000=1),--(c2:c1000="Jan-08"))

If dates:
=sumproduct(--(a2:a1000="John"),--(b2:b1000=1),--(text(c2:c1000,"mmm-yy")="Jan-08"))

Regards,
Fred

 

[Kyle Chastain]

Try using the =Dcount formula instead.


Name Judgement Date
John 1 Jan-08

Name Judgement Date
John 1 Jan-08
Sarah 2 Jan-08
John 1 Feb-08
John 0 Jan-08
John 1 Jan-08

=DCOUNT(A4:C9,"Judgement",A1:C2)
cell A1 is the first name heading, while cell A4 is the second name heading.