| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct (Sumif) with Nested Or Criteria
Can anyone let me know the syntax for including an "OR" criteria inside
SUMPRODUCT (Sumif) function. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Sumif) with Nested Or Criteria
This link might be of help:
http://www.bygsoftware.com/Excel/fun...sumproduct.htm -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "bkt" wrote in message ... Can anyone let me know the syntax for including an "OR" criteria inside SUMPRODUCT (Sumif) function. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Sumif) with Nested Or Criteria
Depends on what type of or.
For a simple count of = A or = A =SUMPRODUCT(--(rng1={"A","B"}) To sum another range with that simple OR =SUMPRODUCT((rng1={"A","B"})*rng3) However, if you want, if rng1 = A OR rng2 5 say, then for a SUM of rng3 meeting these criteria, =SUMPRODUCT(((rng1="A")+(rng25)0)*rng3) you have to be careful not to double count here, when rng1 = A and rng2 5, hence the 0 test See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "bkt" wrote in message ... Can anyone let me know the syntax for including an "OR" criteria inside SUMPRODUCT (Sumif) function. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Sumif) with Nested Or Criteria
Thanks, Gents.
Actually what I am looking for is as below Col. A Col. B Col. C F Blank 10 A Non-Blank 20 Blank Non-Blank 30 F Blank 40 A Non-Blank 30 Blank Blank 20 looking for a Sumproduct formula that will add the quantities in Col. C that has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A and have Non-Blanks in Col. B Eg: 20 + 30 + 30 = 80 Thanks in advance, bkt "Bob Phillips" wrote: Depends on what type of or. For a simple count of = A or = A =SUMPRODUCT(--(rng1={"A","B"}) To sum another range with that simple OR =SUMPRODUCT((rng1={"A","B"})*rng3) However, if you want, if rng1 = A OR rng2 5 say, then for a SUM of rng3 meeting these criteria, =SUMPRODUCT(((rng1="A")+(rng25)0)*rng3) you have to be careful not to double count here, when rng1 = A and rng2 5, hence the 0 test See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "bkt" wrote in message ... Can anyone let me know the syntax for including an "OR" criteria inside SUMPRODUCT (Sumif) function. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Sumif) with Nested Or Criteria
Hi,
Assuming you have the data from A1 till C7 =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="No n-Blanks")*(C2:C7)) Hope this will work for you. Thanks Shail bkt wrote: Thanks, Gents. Actually what I am looking for is as below Col. A Col. B Col. C F Blank 10 A Non-Blank 20 Blank Non-Blank 30 F Blank 40 A Non-Blank 30 Blank Blank 20 looking for a Sumproduct formula that will add the quantities in Col. C that has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A and have Non-Blanks in Col. B Eg: 20 + 30 + 30 = 80 Thanks in advance, bkt "Bob Phillips" wrote: Depends on what type of or. For a simple count of = A or = A =SUMPRODUCT(--(rng1={"A","B"}) To sum another range with that simple OR =SUMPRODUCT((rng1={"A","B"})*rng3) However, if you want, if rng1 = A OR rng2 5 say, then for a SUM of rng3 meeting these criteria, =SUMPRODUCT(((rng1="A")+(rng25)0)*rng3) you have to be careful not to double count here, when rng1 = A and rng2 5, hence the 0 test See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "bkt" wrote in message ... Can anyone let me know the syntax for including an "OR" criteria inside SUMPRODUCT (Sumif) function. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Sumif) with Nested Or Criteria
Maybe I wasn't clear with my question, but Col. B doesn't have to be
Non-Blanks all the time. The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A = Blank, Col. B = Non-Blanks Thanks, bkt "shail" wrote: Hi, Assuming you have the data from A1 till C7 =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="No n-Blanks")*(C2:C7)) Hope this will work for you. Thanks Shail bkt wrote: Thanks, Gents. Actually what I am looking for is as below Col. A Col. B Col. C F Blank 10 A Non-Blank 20 Blank Non-Blank 30 F Blank 40 A Non-Blank 30 Blank Blank 20 looking for a Sumproduct formula that will add the quantities in Col. C that has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A and have Non-Blanks in Col. B Eg: 20 + 30 + 30 = 80 Thanks in advance, bkt "Bob Phillips" wrote: Depends on what type of or. For a simple count of = A or = A =SUMPRODUCT(--(rng1={"A","B"}) To sum another range with that simple OR =SUMPRODUCT((rng1={"A","B"})*rng3) However, if you want, if rng1 = A OR rng2 5 say, then for a SUM of rng3 meeting these criteria, =SUMPRODUCT(((rng1="A")+(rng25)0)*rng3) you have to be careful not to double count here, when rng1 = A and rng2 5, hence the 0 test See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "bkt" wrote in message ... Can anyone let me know the syntax for including an "OR" criteria inside SUMPRODUCT (Sumif) function. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Sumif) with Nested Or Criteria
Hi again,
Better you make a drop down list below each column for the criteria you want to pass for SUMPRODUCT. This will make your SUMPRODUCT to work Dynamic. For this re-write your formula for the SUMPRODUCT as below. Assuming your dropdown lists are at A9, B9 and C9. =sumproduct((A2:A7=A9)*(A2:A7=B9)*(B2:B7=C9)*(C2:C 7)) Hope this will work for you. Thanks, Shail bkt wrote: Maybe I wasn't clear with my question, but Col. B doesn't have to be Non-Blanks all the time. The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A = Blank, Col. B = Non-Blanks Thanks, bkt "shail" wrote: Hi, Assuming you have the data from A1 till C7 =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="No n-Blanks")*(C2:C7)) Hope this will work for you. Thanks Shail bkt wrote: Thanks, Gents. Actually what I am looking for is as below Col. A Col. B Col. C F Blank 10 A Non-Blank 20 Blank Non-Blank 30 F Blank 40 A Non-Blank 30 Blank Blank 20 looking for a Sumproduct formula that will add the quantities in Col. C that has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A and have Non-Blanks in Col. B Eg: 20 + 30 + 30 = 80 Thanks in advance, bkt "Bob Phillips" wrote: Depends on what type of or. For a simple count of = A or = A =SUMPRODUCT(--(rng1={"A","B"}) To sum another range with that simple OR =SUMPRODUCT((rng1={"A","B"})*rng3) However, if you want, if rng1 = A OR rng2 5 say, then for a SUM of rng3 meeting these criteria, =SUMPRODUCT(((rng1="A")+(rng25)0)*rng3) you have to be careful not to double count here, when rng1 = A and rng2 5, hence the 0 test See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "bkt" wrote in message ... Can anyone let me know the syntax for including an "OR" criteria inside SUMPRODUCT (Sumif) function. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Sumif) with Nested Or Criteria
=SUMPRODUCT(((A2:A10="A")+((A2:A10="")*(B2:B10<"" )))*C2:C10)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "bkt" wrote in message ... Maybe I wasn't clear with my question, but Col. B doesn't have to be Non-Blanks all the time. The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A = Blank, Col. B = Non-Blanks Thanks, bkt "shail" wrote: Hi, Assuming you have the data from A1 till C7 =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="No n-Blanks")*(C2:C7)) Hope this will work for you. Thanks Shail bkt wrote: Thanks, Gents. Actually what I am looking for is as below Col. A Col. B Col. C F Blank 10 A Non-Blank 20 Blank Non-Blank 30 F Blank 40 A Non-Blank 30 Blank Blank 20 looking for a Sumproduct formula that will add the quantities in Col. C that has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A and have Non-Blanks in Col. B Eg: 20 + 30 + 30 = 80 Thanks in advance, bkt "Bob Phillips" wrote: Depends on what type of or. For a simple count of = A or = A =SUMPRODUCT(--(rng1={"A","B"}) To sum another range with that simple OR =SUMPRODUCT((rng1={"A","B"})*rng3) However, if you want, if rng1 = A OR rng2 5 say, then for a SUM of rng3 meeting these criteria, =SUMPRODUCT(((rng1="A")+(rng25)0)*rng3) you have to be careful not to double count here, when rng1 = A and rng2 5, hence the 0 test See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "bkt" wrote in message ... Can anyone let me know the syntax for including an "OR" criteria inside SUMPRODUCT (Sumif) function. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Sumif) with Nested Or Criteria
Hi Shail,
I talk of Sumif and you talk of Countif. Cheers, bkt "shail" wrote: Hi again, Better you make a drop down list below each column for the criteria you want to pass for SUMPRODUCT. This will make your SUMPRODUCT to work Dynamic. For this re-write your formula for the SUMPRODUCT as below. Assuming your dropdown lists are at A9, B9 and C9. =sumproduct((A2:A7=A9)*(A2:A7=B9)*(B2:B7=C9)*(C2:C 7)) Hope this will work for you. Thanks, Shail bkt wrote: Maybe I wasn't clear with my question, but Col. B doesn't have to be Non-Blanks all the time. The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A = Blank, Col. B = Non-Blanks Thanks, bkt "shail" wrote: Hi, Assuming you have the data from A1 till C7 =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="No n-Blanks")*(C2:C7)) Hope this will work for you. Thanks Shail bkt wrote: Thanks, Gents. Actually what I am looking for is as below Col. A Col. B Col. C F Blank 10 A Non-Blank 20 Blank Non-Blank 30 F Blank 40 A Non-Blank 30 Blank Blank 20 looking for a Sumproduct formula that will add the quantities in Col. C that has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A and have Non-Blanks in Col. B Eg: 20 + 30 + 30 = 80 Thanks in advance, bkt "Bob Phillips" wrote: Depends on what type of or. For a simple count of = A or = A =SUMPRODUCT(--(rng1={"A","B"}) To sum another range with that simple OR =SUMPRODUCT((rng1={"A","B"})*rng3) However, if you want, if rng1 = A OR rng2 5 say, then for a SUM of rng3 meeting these criteria, =SUMPRODUCT(((rng1="A")+(rng25)0)*rng3) you have to be careful not to double count here, when rng1 = A and rng2 5, hence the 0 test See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "bkt" wrote in message ... Can anyone let me know the syntax for including an "OR" criteria inside SUMPRODUCT (Sumif) function. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Sumif) with Nested Or Criteria
Hi friend,
No it is doing sumif. I did it here over my PC with the example you gave me and the formula I have posted. Do let me know if it is not working with you. Thanks, Shail bkt wrote: Hi Shail, I talk of Sumif and you talk of Countif. Cheers, bkt "shail" wrote: Hi again, Better you make a drop down list below each column for the criteria you want to pass for SUMPRODUCT. This will make your SUMPRODUCT to work Dynamic. For this re-write your formula for the SUMPRODUCT as below. Assuming your dropdown lists are at A9, B9 and C9. =sumproduct((A2:A7=A9)*(A2:A7=B9)*(B2:B7=C9)*(C2:C 7)) Hope this will work for you. Thanks, Shail bkt wrote: Maybe I wasn't clear with my question, but Col. B doesn't have to be Non-Blanks all the time. The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A = Blank, Col. B = Non-Blanks Thanks, bkt "shail" wrote: Hi, Assuming you have the data from A1 till C7 =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="No n-Blanks")*(C2:C7)) Hope this will work for you. Thanks Shail bkt wrote: Thanks, Gents. Actually what I am looking for is as below Col. A Col. B Col. C F Blank 10 A Non-Blank 20 Blank Non-Blank 30 F Blank 40 A Non-Blank 30 Blank Blank 20 looking for a Sumproduct formula that will add the quantities in Col. C that has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A and have Non-Blanks in Col. B Eg: 20 + 30 + 30 = 80 Thanks in advance, bkt "Bob Phillips" wrote: Depends on what type of or. For a simple count of = A or = A =SUMPRODUCT(--(rng1={"A","B"}) To sum another range with that simple OR =SUMPRODUCT((rng1={"A","B"})*rng3) However, if you want, if rng1 = A OR rng2 5 say, then for a SUM of rng3 meeting these criteria, =SUMPRODUCT(((rng1="A")+(rng25)0)*rng3) you have to be careful not to double count here, when rng1 = A and rng2 5, hence the 0 test See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "bkt" wrote in message ... Can anyone let me know the syntax for including an "OR" criteria inside SUMPRODUCT (Sumif) function. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT Criteria Via Cell Reference?? | Excel Worksheet Functions | |||
| Multiple Criteria in SumProduct, N/A Result | Excel Worksheet Functions | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
| "criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
Linear Mode
