How to: Multiply only numbers that contain values higher than 0

[eDarren]

My example here is that I have a sequence of columns within a spreadsheet that data can be input into:

A B C D E F
Blank Blank 3 7 Blank 2

The data input into A, B C D E or F can vary, sometimes it may contain a value, sometimes it may not.

In Column G, I want to have a multiplication of all the values from A B C D E and F BUT only the cells that contain a number, and ignore the cells which do not contain a value (otherwise the answer is always 0 which is inaccurate). If all the columns had values higher than 0, the formula would be simply A x B x C x D x E x F but the formula needs to be able to adapt and change autonomously as sometimes the formula may end up being A X D x F or D x E x F.

I'm looking to utilise VBA for this - does anybody know how I can achieve this?

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G	H
1	2		5		6		60	60
Sheet1

Cell Formulas
Range	Formula
G1	G1	=PRODUCT(IF(A1:F1="",1,A1:F1))
H1	H1	=PRODUCT(FILTER(A1:F1,A1:F1<>""))

 

[eDarren]

Try this:

Book1
	A	B	C	D	E	F	G	H
1	2		5		6		60	60
Sheet1

Cell Formulas
Range	Formula
G1	G1	=PRODUCT(IF(A1:F1="",1,A1:F1))
H1	H1	=PRODUCT(FILTER(A1:F1,A1:F1<>""))

That's worked well. I have noticed though it doesn't seem to like it if the data is spread across different columns throughout the sheet. For example, taking data from C2, P19, T12 and X11. It's not been a problem for me though, i've just re-modelled how the data is presented within the sheet to make sure that the columns which are part of this equation are linearly spread so I can add into the equation C2:H2, for example.

Thanks!