ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   SUMIF multiple criteria (https://www.excelbanter.com/excel-discussion-misc-queries/203941-sumif-multiple-criteria.html)

Tasha
SUMIF multiple criteria
 
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
SUMIF multiple criteria
 
Tasha,

Try this

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

Mike

"Tasha" wrote:

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

Tasha
SUMIF multiple criteria
 
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" wrote:

Tasha,

Try this

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

Mike

"Tasha" wrote:

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
SUMIF multiple criteria
 
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" wrote:

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" wrote:

Tasha,

Try this

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

Mike

"Tasha" wrote:

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

Tasha
SUMIF multiple criteria
 
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" wrote:

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" wrote:

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" wrote:

Tasha,

Try this

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

Mike

"Tasha" wrote:

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
SUMIF multiple criteria
 
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" wrote:

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" wrote:

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" wrote:

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" wrote:

Tasha,

Try this

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

Mike

"Tasha" wrote:

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

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



"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Tasha,

Try this

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

Mike

"Tasha" wrote:

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
SUMIF multiple criteria
 
You're welcome

"Tasha" wrote:

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



"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Tasha,

Try this

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

Mike

"Tasha" wrote:

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

Tasha
SUMIF multiple criteria
 
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??

"Mike H" wrote:

You're welcome

"Tasha" wrote:

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



"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Tasha,

Try this

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

Mike

"Tasha" wrote:

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

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


"Tasha" wrote:

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

"Mike H" wrote:

You're welcome

"Tasha" wrote:

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



"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Tasha,

Try this

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

Mike

"Tasha" wrote:

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


All times are GMT +1. The time now is 06:35 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com