Count with multiple criteria

I needed to sum the total checks and discounts taken by quarter, which I did with this formula:

=SUMPRODUCT(--(Data!CkDate>=DATEVALUE("1/1/2011")),--(Data!CkDate<=DATEVALUE("3/31/2011")),Data!CkAmnt) (Note: substituted DiscAmnt for CkAmnt to sum discounts taken)


I was also able to count the number of checks:
=SUMPRODUCT(--(Data!CkDate>=DATEVALUE("1/1/2011")),--(Data!CkDate<=DATEVALUE("3/31/2011")))

Now I am trying to count checks by customers or checks that I took discounts when paying, using this formula which did NOT work.

=SUMPRODUCT(--AND((Data!CkDate>=DATEVALUE("1/1/2011")),Data!AllowAmnt>0),--AND(Data!CkDate<=DATEVALUE("3/31/2011"),Data!DiscAmnt>0))

The result is always zero (0).

And if I use this formula I get #Value! error:

=SUMPRODUCT(AND(--(Data!CkDate>=DATEVALUE("1/1/2011")),--(Data!CkDate<=DATEVALUE("3/31/2011"))),Data!DiscAmnt>0)

I also used this formula giving zero (0).

=SUMIFS(Data!CKAmnt,Data!CkDate,Data!CkDate>=DATEVALUE("1/1/2011"),Data!CkDate,Data!CkDate<=DATEVALUE("3/31/2011"),Data!DiscAmnt,Data!DiscAmnt>0)

Anybody can figure what I am doing wrong?

I appreciate any help and thanks in advance.

How about?

Please Login or Register  to view this content.