SUMIF multiple columns

[mperemsky]

I am new to excel and functions I am trying to put together a spreadsheet of computer costs. Basically I am listing various mobos, cpus, psus, etc. I have a column that signifies which mobo/cpu/etc I want to include in the actual total cost of the computer. Currently the only way I can perform the summation is to create a long drawn out calculation, which is a pain if I need to add more rows to the spreadsheet. I want to try to do a summation.

So, the grid looks something like the following:

Include Phase Hardware Normal Cost Sale Cost Desription
* 1 Case $70.00 $70.00 Ultra e-torque case
1 Case $55.00 $45.00 Cooler Master Centurian
1 Case $140.00 $100.00 Antec 900
* 1 Mobo $140.00 $140.00 ASUS M4A78T-E
* 2 PSU $130.00 $70.00 OCZ ModXStream
2 PSU $150.00 $100.00 Silverstone
Totals $340.00 $280.00

Current Normal Cost formula:

=IF(LEN(A2),C2)+IF(LEN(A3),C3)+IF(LEN(A4),C4)+IF(LEN(A5),C5)+IF(LEN(A6),C6)+IF(LEN(A7),C7)+IF(LEN(A8),C8)+IF(LEN(A9),C9)+IF(LEN(A10),C10)+IF(LEN(A11),C11)+IF(LEN(A12),C12)+IF(LEN(A13),C13)+IF(LEN(A14),C14)+IF(LEN(A15),C15)+IF(LEN(A16),C16)+IF(LEN(A17),C17)+IF(LEN(A18),C18)

Current Sale Cost formula:

=IF(LEN(A2),D2)+IF(LEN(A3),D3)+IF(LEN(A4),D4)+IF(LEN(A5),D5)+IF(LEN(A6),D6)+IF(LEN(A7),D7)+IF(LEN(A8),D8)+IF(LEN(A9),D9)+IF(LEN(A10),D10)+IF(LEN(A11),D11)+IF(LEN(A12),D12)+IF(LEN(A13),D13)+IF(LEN(A14),D14)+IF(LEN(A15),D15)+IF(LEN(A16),D16)+IF(LEN(A17),D17)+IF(LEN(A18),D18)

I want something simpler to do a summation

SUMIF(A2:A20,"LEN(??)>0",CX)

I don't know how to change this to use the LEN of the current row of the summation or how to take the value for column X of the current summation row.



A next step, I want to try to sum up values in separate phases. As it will take me some time to save up the cost of the whole machine at once, I thought I could do it in phases. This would require a summation IF LEN of 1st column was not blank AND Phase = 1 or 2, etc

Total Phase 1 = SUM(A2:A20,"LEN()>0 AND BX='1') where X is the row of the summation
Total Phase 1 = SUM(A2:A20,"LEN()>0 AND BX='2') where X is the row of the summation


Any help trying to resolve this would be greatly appreciated. Even if you can tell me I am doing this wrong and there is a better way to do this I would appreciate it.

TIA

Mike

[Paul]

Hello Mike, and welcome to the forum.

Perhaps you could upload a sample worksheet so we can better see the layout of your data (the forum post doesn't do it justice). We would need to see the current layout as well as your expected results.

Thanks!

[tigertiger]

SUMIF(A2:A20,"LEN(??)>0",CX)

Option 1: - using a dummy
YOU can make a dummy column calculating LEN(A..) - column E for example

at E2:
=LEN(A2)
copy down to E3:E20
and the total: =SUMIF(E3:E20,">0",C2:C20)


Option 2: - using SUMPRODUCT function

=SUMPRODUCT((LEN(A2:A20)>0)*C2:C20)

Total Phase 1 = SUM(A2:A20,"LEN()>0 AND BX='1') where X is the row of the summation
Total Phase 1 = SUM(A2:A20,"LEN()>0 AND BX='2') where X is the row of the summation

If need more conditional for Bx, the corresponding formula 'd be used:

=SUMPRODUCT((LEN(A2:A20)>0)*(B2:B20=1)*C2:C20)

[DonkeyOte]

As Paul states a sample would be helpful for us to ascertain the data format and layout... if for ex. A is a string you can use a SUMIF for your first calc:

=SUMIF(A2:A19,"*",C2:C19)

[mperemsky]

I was going to post my example, but thought I would try the suggestions first.

All 3 suggestions worked!

I decided to go with the last one =SUMIF(A2:A18,"*",D2:D18) as it did not require extra calls to LEN or extra columns.

Thank you guys, this is great! Now I can com eup with more scenarios for purchasing a new computer (that the wife seems to think I don't need!). :-P

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save