#VALUE! when summing the products of two column

[arki]

Hi

How can I make my formula work when summing the products of two column on the attached table? I am trying to calculate the cost of my product (E78) by summing (C54:C76) with (E54:E76). It returns #VALUE! error.

I believe it is to do with some text in between the selected area.

Please help, I don't want to do it like D78 by adding them individually.

Thanks!

[Mordred]

I does have to do with the text in the cells. You have it set to be text in your if statements running through each cell. I take it you want the cells to appear empty if no used? You could always set the value to 0 and the font color to white if the cells are greater than 0.

[nawas]

Try this

=SUMPRODUCT(IF(ISERROR(C54:C76*E54:E76),"",(C54:C76*E54:E76))

Confirmed with CTRL+SHIFT+ENTER

Thanks

[Mordred]

I am not worthy of formulas in the spreadsheet. Nice one!

[Haseeb Avarakkan]

Hello,

Also try this;

=SUMPRODUCT((0&C54:C76)*(0&E54:E76))

Your original formula will work, if you change these formulas,

C54, copy down.

=IF(C5>0.00001,C5,0)

E54, copy down.

=IF(E5>0.00001,E5*E$53,0)

Then use this Custom cell format to hide zero values;

0.00;-0.00;;