| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple variables
hi everyone,
here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
Based on your sample data this portion returns 0:
=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6) And this portion returns 5: SUMPRODUCT((A1:A6="C")*(B1:B6="D"),C1:C6) Combined, the result is 5. So, I have no idea what this means: = 1 + 5 = 6 -- Biff Microsoft Excel MVP "sam" wrote in message ... hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
Try the below
=SUMPRODUCT(ISNUMBER(MATCH(A1:A6,{"A","C"},0))* ISNUMBER(MATCH(B1:B6,{"B","D"},0)),C1:C6) If this post helps click Yes --------------- Jacob Skaria "sam" wrote: hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
That doesn't meet the criteria:
It is essential that A is matched with B and that C is matched with D. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below =SUMPRODUCT(ISNUMBER(MATCH(A1:A6,{"A","C"},0))* ISNUMBER(MATCH(B1:B6,{"B","D"},0)),C1:C6) If this post helps click Yes --------------- Jacob Skaria "sam" wrote: hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
Doesn't row 1 return 1 (not 0) from first formula, Biff?
-- David Biddulph "T. Valko" wrote in message ... Based on your sample data this portion returns 0: =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6) And this portion returns 5: SUMPRODUCT((A1:A6="C")*(B1:B6="D"),C1:C6) Combined, the result is 5. So, I have no idea what this means: = 1 + 5 = 6 -- Biff Microsoft Excel MVP "sam" wrote in message ... hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
=SUMPRODUCT((A1:A6&B1:B6="AB")+(A1:A6&B1:B6="CD"), C1:C6)
(assuming that each column has only a single character and thus that you can't get AB or CD in one column and the other blank). -- David Biddulph "sam" wrote in message ... hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
Ooops!
Yes it does. When I copied/pasted the data into a file I somehow missed row 1. My row 1 was: A C 2 -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Doesn't row 1 return 1 (not 0) from first formula, Biff? -- David Biddulph "T. Valko" wrote in message ... Based on your sample data this portion returns 0: =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6) And this portion returns 5: SUMPRODUCT((A1:A6="C")*(B1:B6="D"),C1:C6) Combined, the result is 5. So, I have no idea what this means: = 1 + 5 = 6 -- Biff Microsoft Excel MVP "sam" wrote in message ... hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
You can also use
=SUMPRODUCT((A1:A6&B1:B6={"AB","CD"})*C1:C6) -- __________________________________ HTH Bob "sam" wrote in message ... hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
Try this:
=SUMPRODUCT((A1:A6={"A","C"})*(B1:B6={"B","D"})*C1 :C6) "sam" wrote: hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
Try Pivot table
"sam" wrote: hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with multiple variables
You are right...I missed that..
"T. Valko" wrote: That doesn't meet the criteria: It is essential that A is matched with B and that C is matched with D. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below =SUMPRODUCT(ISNUMBER(MATCH(A1:A6,{"A","C"},0))* ISNUMBER(MATCH(B1:B6,{"B","D"},0)),C1:C6) If this post helps click Yes --------------- Jacob Skaria "sam" wrote: hi everyone, here is my issue. i am using SUMPRODUCT for a couple of criteria and returning the sum of another column. E.g. A B 1 A C 2 B C 3 B E 4 C A 5 C D 5 =SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6) = 1 + 5 = 6 This all works fine but I was wondering if there was another option so i would not have to use SUMPRODUCT multiple times. It is essential that A is matched with B and that C is matched with D. Thanks Sam . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct/sum not working with multiple variables | Excel Worksheet Functions | |||
| Using SUMPRODUCT, 3 variables, 2 types of data in 1 column | Excel Worksheet Functions | |||
| sumproduct with 3 variables | Excel Discussion (Misc queries) | |||
| multiple variables in sumproduct or if/then formulas | Excel Worksheet Functions | |||
| look for a value with multiple variables | Excel Discussion (Misc queries) | |||
Linear Mode
