# Help in SUMPRODUCT formula...

A

#### 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"?

J

#### JE McGimpsey

One way:

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

F

#### 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

K

#### 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.