SUMIF Statements

[Brian]

Can anyone help with this problem

I have a spreadsheet with 4 columns. These columns are Cost, Yes/No, Area and Cost Code

I want to work out costs to each combination of Yes/No, Area and Cost Cod

e.g

Cost Yes/No Area Cost Cod

£200 Y Car 123
£100 N Car 123
£100 Y House 2345
£200 N Travel 234
£300 Y Car 123

This should return costs of

Car 1234 Y £400
Car 1234 N £10
House 2345 Y £10
Travel 2345 N £20

I have been trying to use SUMIF statements for this but I can't get it to work as I think SUMIF will only sum the Cost column based on one criteria ie Area, Yes/No or Cost Code – not a combination of all three

 

[Roger Govier]

Hi Brian

Assuming your data is in A21000

In cells F1:H4 set up your criteria as you have shown them

in Cell I1
=SUMPRODUCT((C2:C1000=F1)*(D21000=G1)*(B2:B1000=H1)*A2:A1000)

Copy down through I2:I4


--
Regards
Roger Govier

Can anyone help with this problem?

I have a spreadsheet with 4 columns. These columns are Cost, Yes/No, Area and Cost Code.

I want to work out costs to each combination of Yes/No, Area and Cost Code

e.g.

Cost Yes/No Area Cost Code

£200 Y Car 1234
£100 N Car 1234
£100 Y House 2345
£200 N Travel 2345
£300 Y Car 1234

This should return costs of:

Car 1234 Y £400
Car 1234 N £100
House 2345 Y £100
Travel 2345 N £200

I have been trying to use SUMIF statements for this but I can't get it to

work as I think SUMIF will only sum the Cost column based on one criteria ie
Area, Yes/No or Cost Code - not a combination of all three.

 

[Bernard Liengme]

Hello,
Let the input data be in A15
Let the output table start in A9 (A9=Car, B9=1234,C9=Y...)
In D9 enter
=SUMPRODUCT(($C$1:$C$5=A9)*($D$1:$D$5=B9)*($B$1:$B$5=C9)*($A$1:$A$5))
Copy down the column.
Alternately, make a Pivot Table
By-the-by your results are incorrect - first row is 500.
Best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

Can anyone help with this problem?

I have a spreadsheet with 4 columns. These columns are Cost, Yes/No, Area and Cost Code.

I want to work out costs to each combination of Yes/No, Area and Cost Code

e.g.

Cost Yes/No Area Cost Code

£200 Y Car 1234
£100 N Car 1234
£100 Y House 2345
£200 N Travel 2345
£300 Y Car 1234

This should return costs of:

Car 1234 Y £400
Car 1234 N £100
House 2345 Y £100
Travel 2345 N £200

I have been trying to use SUMIF statements for this but I can't get it to

work as I think SUMIF will only sum the Cost column based on one criteria ie
Area, Yes/No or Cost Code - not a combination of all three.

 

[Brian]

Thanks for this Guys. I tried pivot table method Bernard and it gave me exactly what I was looking for, unfortunately, I can't use that as I need to use the totals generated in an equation. I can't reference a cell in a pivot table as it changes with data updates. Unless you happen to know a way of doing that

Brian