SUMIF multiple criteria

[Tasha]

I know this question has been asked over and over again, but I can't find an
answer that fits my needs. I have a range of status(ERDS), a range of
dates(ERDAY) and the column I need summed is ERQTY. I need the ERDS to equal
2, 43, 64 or 66, and the date to equal cell D2, then summing ERQTY if they
match the criteria. Can someone help me with this? This is the formula I've
used and am getting 0
=SUMPRODUCT((ERDAY=D$2)*(ISNUMBER(MATCH(ERDS,{2;64;43;66},ERQTY))))

 

[Mike H]

Tasha,

Try this

=SUMPRODUCT((Erday=D$2)*(Erds={2,64,43,66})*(Erqty))

Mike

 

[Tasha]

Well, I didn't get a 0 this time, but is giving me too many. I think because
the ERQTY should be summed??? how would the formula read then?

 

[Mike H]

Tasha

The formula will sum ERQTY prroved that the date =d2 and it finds any of
2,64,43,66 in ERDS.

My data look like this
ErDay Erds Erqty
01/01/2008 2 99


02/01/2008 66 99

With 1/1/2008 in D2 I get 99 if I change the other date to match d2 I get 198

What are you getting?

Mike

 

[Tasha]

This is an example of my data and what results I got: **note....ERDAY is the
date 9/1/2008 formated as 'd'

PAT ERDS ERDAY ERQTY
2055 1 1 1
2056 2 2 1
2057 2 3 1
2057 2 3 -1
2058 2 3 1
2059 20 3 1
totals should be:
1st=0 2nd=1 3rd=1
There are no 43's for those days.
What I am getting from your formula is this:
1st=0 2nd=1 3rd=3

 

[Mike H]

This formula gives me the results you expect

=SUMPRODUCT((erday=E2)*(Erds={2,64,43,66})*(erqty))

Note I change the ref cell to E2 because you seem to have data in column D

Mike

 

[Tasha]

That worked, I'm sorry, I had the wrong cell referenced .... it worked
wonderful!!!! Thank you thank you thank you!!!

 

[Mike H]

You're welcome

That worked, I'm sorry, I had the wrong cell referenced .... it worked
wonderful!!!! Thank you thank you thank you!!!

 

[Tasha]

Ok, one more ?. If on this same sheet I don't want to count by ERDAY, how
would I do that? I just want a sum of ERQTY if ERDS=2,43,66,64??

 

[Tasha]

Figured it out
=SUMPRODUCT((ERDAY<AG2)*(ERDS={2,64,43,66})*(ERQTY))
AG2=the next month's begin date....