sumif on a single criteria rows but several sum rows

[Dazzadata]

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

 

[Per Jessen]

Hi

This should do it:

=SUMPRODUCT((A1:L1=TRUE)*1,A3:L3,A4:L4,A5:L5,A6:L6,A7:L7)

Regards,
Per

 

[Dazzadata]

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]

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