sumif on a single criteria rows but several sum rows

  • Thread starter Thread starter Dazzadata
  • Start date Start date
D

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
 
Hi

This should do it:

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

Regards,
Per
 
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?
 
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
 
Back
Top