Formula in cells

[RJ1969]

Hi,
I was trying to get a formula that will show zero until I placed the number in the J3 cells. That something like this =IF(SUM(C9:G9)=0,"",SUM(C9:G9))

I want to accomplish something like this. Example: There is a column of $2 and $4. If I put 2 in column $2 then the total up should be 4 and if I put 2 in the $4 column then that should be the total of $8.00.

The sample example worksheet. The total should be $20 under the balance. Because there is 2 of ($2 columns) and 4 ($4 columns).

Thank you in advance.

[Norie]

Try this.

=IF(COUNT(B3:I3), SUMPRODUCT($B$2:$I$2,B3:I3), "")

[etaf]

=sumproduct((b3:i3*$b$2:$i$2))

[RJ1969]

Hi Etaf

How did you get this { } after you enter the formula?

thanks.

[etaf]

it should not need an array , thats what the {} shows , but my version of excel 365 now decides itself , whereas before it would need entering

previous version you would use
(Ctrl + Shift + Enter)
in the formula bar
to get the } brackets

[RJ1969]

Etaf,
It worked! Yay!

Request, can you make the cell J3 Balance not visible until I enter the item?

Thanks.

[etaf]

as posted by Norie

=IF(COUNTA(B3:I3),SUMPRODUCT((B3:I3),($B$2:$I$2)),"")

[RJ1969]

Hi Etaf,
I don't what happened. I change $2 to 2 oz but I get the #value Result.

I only put the $2 and $4 as a title as a sample but when I changed it it difers the result.

Sorry, The original is actually 2 oz and 4 oz.

thanks.

[etaf]

the 2 was a number , formatted with a $
BUT now you have TEXT with the OZ, so will NOT work

Maybe able to extract just the first character , assuming its always going to be 1 character

[etaf]

try
=SUMPRODUCT(B3:I3*LEFT($B$2:$I$2,1))

[RJ1969]

It worked!

Thank you Etaf.

and Thank you Norie.

[etaf]

you are welcome

[RJ1969]

Hi etaf,
Sorry, there's a change.

Please Login or Register  to view this content.

2 oz is going to be $4.00 and the 4 oz is going to $8.00

How do I change the formula?

Thanks.

[Norie]

You seem to have changed some other things too, for example the row the cost per unit is coming from.

In the original workbook that was row 3, now it looks like you are using row 1.

Can you upload a new workbook?

[RJ1969]

Hi,
I think I got it. Thank you Norie and thank you all.