| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
| Multiple Criteria for SUMIF | Excel Worksheet Functions | |||
| Sumif with Multiple Criteria | Excel Worksheet Functions | |||
| Sumif with multiple criteria | Excel Worksheet Functions | |||
| SUMIF multiple criteria | Excel Discussion (Misc queries) | |||
Linear Mode
