Multiply multiple columns with blank values, ignore

[wherdzik]

I dont know why this is so difficult but In the attached file you will find a table which I would like to perform a multiplcation formula but to multiple columns and some are non-adjacent. This issue I am running into is that column G,H,I,J has null values which is creating an error. The other issue I am running into is if there are no values in column G,H,I,J and there is a value in column M or O. It will make the formula in column N or column P = 0 and I would just like the value of M or O if this is the case.


Requesting 2 formulas in column N and in column P

1. Column N:
Multiply g,h,i,j,l,m


2. Column P
Multiply column G,H,I,J,L,O

Examples of formula output.

Line 22, the result value in N and P should be 0.16.

Line 4, the formula out put for both columns should be 0

[Pepe Le Mokko]

Line 22, the result value in N and P should be 0.16.

How can you obtain these results as e.g. G22 =0 ? The product would be 0, not 0.16
It is not the null values that throw the error, it is the empty text string in col I
So, some more explaining of your expected results would help

[wherdzik]

I removed 0 values from column G inthe file and reattachedto make it easier. I will do this in my master file.


Here are the parameters:


1. Ignore blanks in column g,H,I,J and only use numerical values to multiply together.
2. If there are values greater than 0 in column L but Column M displays zero then equal 0.


Esentially Multiply G,H,I,J,L,M BUT if there are 0 values in M or O then display 0.

I hope I did a better job this time.

[windknife]

Maybe,

N2 =PRODUCT(IF(G2:J2<>"",G2:J2,1),L2,M2)
P2 =PRODUCT(IF(G2:J2<>"",G2:J2,1),L2,O2)

commited by Ctrl+Shift+Enter. copied down.

[wk9128]

Cell N2 formula , Drag down

Please Login or Register  to view this content.

Cell P2 formula , Drag down

Please Login or Register  to view this content.