| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
sumif
still trying to find a way to use 2 criteria within 2 ranges to add 1 range
of data; range1 range2 criteria1 criteria2 data =sumif(range1,criteria1,data)+sumif(range2,criteri a2,data) will give twice the amount |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumif
Hi Sulasno,
I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in that way. For that you could use the SUMPRODUCT function (see the other replies), or: =SUM(IF((range1=criteria1)*((range2=criteria2),dat a)) as an array formula (entered with <Ctrl-<Shift-<Enter). Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or whole-row references (ie E:E or 3:3 won't work). Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Sulasno" wrote in message ... still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data; range1 range2 criteria1 criteria2 data =sumif(range1,criteria1,data)+sumif(range2,criteri a2,data) will give twice the amount |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumif
thanks
I am trying to understand what does the * means going to try this forumula; =sum(if((A1:A100,"XXX")*((B1:B100,"YYY"),C1:C100)) and use <Ctrl-<Shift-<Enter) can "XXX" and "YYY" point to a value in a cell? =sum(if((A1:A100,X1((B1:B100,Y1),C1:C100)) and use <Ctrl-<Shift-<Enter) "macropod" wrote in message ... Hi Sulasno, I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in that way. For that you could use the SUMPRODUCT function (see the other replies), or: =SUM(IF((range1=criteria1)*((range2=criteria2),dat a)) as an array formula (entered with <Ctrl-<Shift-<Enter). Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or whole-row references (ie E:E or 3:3 won't work). Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Sulasno" wrote in message ... still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data; range1 range2 criteria1 criteria2 data =sumif(range1,criteria1,data)+sumif(range2,criteri a2,data) will give twice the amount |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumif
thanks
finally got it right {=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))} "macropod" wrote in message ... Hi Sulasno, I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in that way. For that you could use the SUMPRODUCT function (see the other replies), or: =SUM(IF((range1=criteria1)*((range2=criteria2),dat a)) as an array formula (entered with <Ctrl-<Shift-<Enter). Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or whole-row references (ie E:E or 3:3 won't work). Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Sulasno" wrote in message ... still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data; range1 range2 criteria1 criteria2 data =sumif(range1,criteria1,data)+sumif(range2,criteri a2,data) will give twice the amount |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumif
thanks
is there a limit to the number of criteria? I presume that this will also work? =SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C $7=C2)*($D$2:$D$7=D2),$E$2:$E$7) "Don Guillett" wrote in message ... Less resource use with sumproduct. =SUMproduct(($A2:$A$7=A2)*($B$2:$B$7=B2),$E$2:$E$7 ) -- Don Guillett Microsoft MVP Excel SalesAid Software "Sulasno" wrote in message ... thanks finally got it right {=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))} "macropod" wrote in message ... Hi Sulasno, I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in that way. For that you could use the SUMPRODUCT function (see the other replies), or: =SUM(IF((range1=criteria1)*((range2=criteria2),dat a)) as an array formula (entered with <Ctrl-<Shift-<Enter). Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or whole-row references (ie E:E or 3:3 won't work). Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Sulasno" wrote in message ... still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data; range1 range2 criteria1 criteria2 data =sumif(range1,criteria1,data)+sumif(range2,criteri a2,data) will give twice the amount |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumif
You should always try it yourself first. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sulasno" wrote in message ... thanks is there a limit to the number of criteria? I presume that this will also work? =SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C $7=C2)*($D$2:$D$7=D2),$E$2:$E$7) "Don Guillett" wrote in message ... Less resource use with sumproduct. =SUMproduct(($A2:$A$7=A2)*($B$2:$B$7=B2),$E$2:$E$7 ) -- Don Guillett Microsoft MVP Excel SalesAid Software "Sulasno" wrote in message ... thanks finally got it right {=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))} "macropod" wrote in message ... Hi Sulasno, I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in that way. For that you could use the SUMPRODUCT function (see the other replies), or: =SUM(IF((range1=criteria1)*((range2=criteria2),dat a)) as an array formula (entered with <Ctrl-<Shift-<Enter). Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or whole-row references (ie E:E or 3:3 won't work). Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Sulasno" wrote in message ... still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data; range1 range2 criteria1 criteria2 data =sumif(range1,criteria1,data)+sumif(range2,criteri a2,data) will give twice the amount |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumif
I wish I could; I don't have the file in hand and is helping someone with
the question "Don Guillett" wrote in message ... You should always try it yourself first. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sulasno" wrote in message ... thanks is there a limit to the number of criteria? I presume that this will also work? =SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C $7=C2)*($D$2:$D$7=D2),$E$2:$E$7) "Don Guillett" wrote in message ... Less resource use with sumproduct. =SUMproduct(($A2:$A$7=A2)*($B$2:$B$7=B2),$E$2:$E$7 ) -- Don Guillett Microsoft MVP Excel SalesAid Software "Sulasno" wrote in message ... thanks finally got it right {=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))} "macropod" wrote in message ... Hi Sulasno, I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in that way. For that you could use the SUMPRODUCT function (see the other replies), or: =SUM(IF((range1=criteria1)*((range2=criteria2),dat a)) as an array formula (entered with <Ctrl-<Shift-<Enter). Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or whole-row references (ie E:E or 3:3 won't work). Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Sulasno" wrote in message ... still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data; range1 range2 criteria1 criteria2 data =sumif(range1,criteria1,data)+sumif(range2,criteri a2,data) will give twice the amount |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumif
Hi Sulasno,
The tests (range1=criteria1) and (range2=criteria2) both return True/False (1/0) values according to whether the test is satisfied. So each test ends up with and array of 1s and 0s. The * multiples the 1s and 0s from the two arrays. The result is that you end up with 1s (ie True results) where the values in both arrays are 1s (ie 1*1=1, 1*0=0, 0*1=0, 0*0=0). In essence, then, * works like an AND test - basically requiring both conditions to be true. Sorry about the typo in the formula. It should have been: =SUM(IF((range1=criteria1)*(range2=criteria2),data )) And yes, you can add more conditions, for example: =SUM(IF((range1=criteria1)*(range2=criteria2)*(ran ge2criteria1)*(range1<criteria2),data)) Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Sulasno" wrote in message ... thanks I am trying to understand what does the * means going to try this forumula; =sum(if((A1:A100,"XXX")*((B1:B100,"YYY"),C1:C100)) and use <Ctrl-<Shift-<Enter) can "XXX" and "YYY" point to a value in a cell? =sum(if((A1:A100,X1((B1:B100,Y1),C1:C100)) and use <Ctrl-<Shift-<Enter) "macropod" wrote in message ... Hi Sulasno, I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in that way. For that you could use the SUMPRODUCT function (see the other replies), or: =SUM(IF((range1=criteria1)*((range2=criteria2),dat a)) as an array formula (entered with <Ctrl-<Shift-<Enter). Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or whole-row references (ie E:E or 3:3 won't work). Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Sulasno" wrote in message ... still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data; range1 range2 criteria1 criteria2 data =sumif(range1,criteria1,data)+sumif(range2,criteri a2,data) will give twice the amount |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
| SumIf | Excel Discussion (Misc queries) | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
![Sulasno[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
