|
Limited Sumif
Hello all,
I am looking for a kind of Sumif function to sum the values in column A if the conditions in column B are OK. BUT the range should be limited. Sumif sums all the values in the range which match a condition, while I am looking for some function which limits the range (as specified). In the example below, when you drop down from C1, the values from column A should be summed in column C once you reach the first '1' for all B's=0, but only for the previous 'block' of zeros. I hope the example makes it a bit more clear. A B C 0,1 0 0 0,1 0 0 0,1 0 0 0,1 1 0,3 (=sum A1-A3) 0,1 1 0 0,1 1 0 0,1 0 0 0,1 0 0 0,1 0 0 0,1 0 0 0,1 1 0,4 (=sum A7-A10) .... thanks! Maarten |
Let A1:B15 house:
{"X",1; 0.1,0; 0.1,0; 0.1,0; 0.1,1; 0.1,1; 0.1,1; 0.1,0; 0.1,0; 0.1,0; 0.1,0; 0.1,1; 0.1,1; 0.1,0; 0.1,0} On you system, a value like 0.1 is 0,1. Note that A1:B1 houses: X and 1. X is a label, while 1 is required. Suppose that you have a label in B1, like Y. Replace Y with 1 and custom format B1 as: [=1]"Y";General Now B1 will display Y but it will house 1, which is needed by the formula in C2 that follows... C2: =IF((B1=0)*(B2=1),SUM(A1:INDEX($A$1:A1,MATCH(2,1/($B$1:B1=1))+1)),"") which you need to confirm with control+shift+enter (instead of just with enter) and copy down. Maarten wrote: Hello all, I am looking for a kind of Sumif function to sum the values in column A if the conditions in column B are OK. BUT the range should be limited. Sumif sums all the values in the range which match a condition, while I am looking for some function which limits the range (as specified). In the example below, when you drop down from C1, the values from column A should be summed in column C once you reach the first '1' for all B's=0, but only for the previous 'block' of zeros. I hope the example makes it a bit more clear. A B C 0,1 0 0 0,1 0 0 0,1 0 0 0,1 1 0,3 (=sum A1-A3) 0,1 1 0 0,1 1 0 0,1 0 0 0,1 0 0 0,1 0 0 0,1 0 0 0,1 1 0,4 (=sum A7-A10) ... thanks! Maarten |
It works fine, Thanks a lot!
"Aladin Akyurek" wrote: Let A1:B15 house: {"X",1; 0.1,0; 0.1,0; 0.1,0; 0.1,1; 0.1,1; 0.1,1; 0.1,0; 0.1,0; 0.1,0; 0.1,0; 0.1,1; 0.1,1; 0.1,0; 0.1,0} On you system, a value like 0.1 is 0,1. Note that A1:B1 houses: X and 1. X is a label, while 1 is required. Suppose that you have a label in B1, like Y. Replace Y with 1 and custom format B1 as: [=1]"Y";General Now B1 will display Y but it will house 1, which is needed by the formula in C2 that follows... C2: =IF((B1=0)*(B2=1),SUM(A1:INDEX($A$1:A1,MATCH(2,1/($B$1:B1=1))+1)),"") which you need to confirm with control+shift+enter (instead of just with enter) and copy down. Maarten wrote: Hello all, I am looking for a kind of Sumif function to sum the values in column A if the conditions in column B are OK. BUT the range should be limited. Sumif sums all the values in the range which match a condition, while I am looking for some function which limits the range (as specified). In the example below, when you drop down from C1, the values from column A should be summed in column C once you reach the first '1' for all B's=0, but only for the previous 'block' of zeros. I hope the example makes it a bit more clear. A B C 0,1 0 0 0,1 0 0 0,1 0 0 0,1 1 0,3 (=sum A1-A3) 0,1 1 0 0,1 1 0 0,1 0 0 0,1 0 0 0,1 0 0 0,1 0 0 0,1 1 0,4 (=sum A7-A10) ... thanks! Maarten |
| All times are GMT +1. The time now is 10:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com