Thread: Help using SUM, PRODUCT and IF in a formula

Hello I am new to the forum and have minimal skills in excel and need some help!

I am currently doing a project where I need to calculate various quantities for a building project into a budget. I am trying to get a formula that can calculate the totals of specific columns regardless if one column has a zero value inserted.

The column description are as follows:

C: No. (for instance number of doors or wash basins) <if column C is being used columns D,E,F are not used, visa versa>

D,E and F are dimension columns e.g (the area of tiles or depth of excavation) and are multiplied out

D: Length
E: Width (may or may not be used given if a measurement is in lineal meters, m2 or m3)
F: Height (may or may not be used given if a measurement is in m2 or m3)

G: Times (multiplies the totaled value if its a repeated element or set) will always be greater or equal to 1


I am stuck on how to get a formula to calculate it out properly, and just been scouring the net and help guides.

=SUMIF(C12:F12,">0")*(G12) This is the formula I have been playing around and using this to calculate, which works for half my problem but not the other half!

Trial data: using =SUMIF(C12:F12,">0")*(G12)

example1: (just using a No. calc) C: 4 D: 0 E: 0 F: 0 G: 2 RESULT: 8 WANT: 8

example2: (using a LxWxH calc) C: 0 D: 3 E: 2 F: 2 G: 2 RESULT: 14 WANT: 24

example2: (using a LxW calc) C: 0 D: 3 E: 2 F: 0 G: 3 RESULT: 10
WANT: 12

I don't have an issue when I enter a zero value, the issue is getting columns D,E,F to times out instead of add together.

Any ideas on a correct formula? (i am stumped for an answer)
Am I approaching the problem in the right way? or do I need to use two separate formulas?

I can solve my problem with two formulas.... but I am trying to keep the amount of formulas to a minimum to save time so I can just insert the data without having to copy and paste different formulas...

If anyone can help me that would be greatly appreciated!!!!

try perhaps:

=PRODUCT(IF(C12:F12>0,C12:F12))&G12

or

=PRODUCT(IF(C1:G1>0,C1:G1))

either formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER