|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 02:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com