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