Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif on a single criteria rows but several sum rows
This sounds simple but is proving tricky
I have row of booleans a1:l1 and want to sum rows below that a3:l7 according to the booleans value, but how could this be done in 1 cell sumif wont work because the ranges are different shapes so it only does row 3. I think the problem is row/column ambiguity of course you could sub total rows or columns or do multiple sumifs in one cell, but is there a better way? Cheers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif on a single criteria rows but several sum rows
Hi
This should do it: =SUMPRODUCT((A1:L1=TRUE)*1,A3:L3,A4:L4,A5:L5,A6:L6 ,A7:L7) Regards, Per "Dazzadata" skrev i meddelelsen ... This sounds simple but is proving tricky I have row of booleans a1:l1 and want to sum rows below that a3:l7 according to the booleans value, but how could this be done in 1 cell sumif wont work because the ranges are different shapes so it only does row 3. I think the problem is row/column ambiguity of course you could sub total rows or columns or do multiple sumifs in one cell, but is there a better way? Cheers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif on a single criteria rows but several sum rows
Hi Per
agreed, but it wouldn't work if you wanted to sum 1000 rows, or insert a row not trying to be an arse just curious....how could you do it using the range 'a3:l7' stated as such? "Per Jessen" wrote: Hi This should do it: =SUMPRODUCT((A1:L1=TRUE)*1,A3:L3,A4:L4,A5:L5,A6:L6 ,A7:L7) Regards, Per "Dazzadata" skrev i meddelelsen ... This sounds simple but is proving tricky I have row of booleans a1:l1 and want to sum rows below that a3:l7 according to the booleans value, but how could this be done in 1 cell sumif wont work because the ranges are different shapes so it only does row 3. I think the problem is row/column ambiguity of course you could sub total rows or columns or do multiple sumifs in one cell, but is there a better way? Cheers |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif on a single criteria rows but several sum rows
Hi
OK I see your point, so I created a function to do what you require. ALT+F11 to open the macro editor Insert Module. Copy the code below to the code sheet. To use the function enter =SumBoolean in the desired cell and click on the equalsign left to the fomula line. Fill in the required values. Function SumBoolean(BooleanValue As Boolean, BooleanRow As Range, SumRange As Range) As Double fRow = SumRange.Cells(1, 1).Row lRow = SumRange.Rows.Count + fRow - 1 For Each cell In BooleanRow.Cells If cell.Value = BooleanValue Then total = total + Application.WorksheetFunction.Sum(Range(Cells(fRow , cell.Column), Cells(lRow, cell.Column))) End If Next SumBoolean = total End Function Best regards, Per "Dazzadata" skrev i meddelelsen ... Hi Per agreed, but it wouldn't work if you wanted to sum 1000 rows, or insert a row not trying to be an arse just curious....how could you do it using the range 'a3:l7' stated as such? "Per Jessen" wrote: Hi This should do it: =SUMPRODUCT((A1:L1=TRUE)*1,A3:L3,A4:L4,A5:L5,A6:L6 ,A7:L7) Regards, Per "Dazzadata" skrev i meddelelsen ... This sounds simple but is proving tricky I have row of booleans a1:l1 and want to sum rows below that a3:l7 according to the booleans value, but how could this be done in 1 cell sumif wont work because the ranges are different shapes so it only does row 3. I think the problem is row/column ambiguity of course you could sub total rows or columns or do multiple sumifs in one cell, but is there a better way? Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Moving data from multiple rows to single rows | Excel Worksheet Functions | |||
Sum single or duplicate rows with multiple criteria | Excel Worksheet Functions | |||
Multi rows to single row | Excel Discussion (Misc queries) | |||
Multiple rows from a single row | Excel Discussion (Misc queries) |