Formula help

[RattlingCarp3048]

i am trying to build a formula to do a calculation between 4 columns where any one of the values could be missing at any given time. I want the formula to only calculate when values are present. I know how to build using the if function but there are so many possible combinations that im not sure how to capture them all. If anyone knows of an easier way could you please help? The values of the cells are being returned via formula that cannot be removed (it would be so much simpler if i could). Here is a sample.... this does not reflect every single possible combination, just a sample.

column Q	Column R	Columns S	Column T
(Q)	(R*3)	(S*2)	(T)		<<calculation for each column
Counted	Missing	Manual	Electronic	Expected Result	vvv calculation being performed
4	8	12	20	72	((Q+(R*3))+((T+(S*2))
4	8	12		52	((Q+(R*3))+(S*2))
4	8			28	(Q+(R*3)
4				4	Q
4	8		20	48	((Q+(R*3))+T)
4		12	20	48	(Q+((T+(S*2))
	8		20	44	((R*3)+T)
		12	20	44	T+(S*2)
	8	12	20	68	((R*3)+((T+(S*2))
4			20	24	(Q+T)

 

[etaf]

maybe i dont follow - but you are only multiplying 1 value at a time and that adding other - so if a cell is blank - it does not affect others

for example (Q2+R2)*S2 - if S2 is blank - it will multiply by zero, and so give a result of zero
if it was ignored then it would give Q2+R2



i just used
=((Q2+(R2*3))+((T2+(S2*2))))
and it gave all the expected results - see column Y with formula

perhaps i'm missing something

Book2
	Q	R	S	T	U	V	W	X	Y
1	Counted	Missing	Manual	Electronic	Expected Result	vvv calculation being performed
2	4	8	12	20	72	((Q+(R*3))+((T+(S*2))			72
3	4	8	12		52	((Q+(R*3))+(S*2))			52
4	4	8			28	(Q+(R*3)			28
5	4				4	Q			4
6	4	8		20	48	((Q+(R*3))+T)			48
7	4		12	20	48	(Q+((T+(S*2))			48
8		8		20	44	((R*3)+T)			44
9			12	20	44	T+(S*2)			44
10		8	12	20	68	((R*3)+((T+(S*2))			68
11	4			20	24	(Q+T)			24
Sheet1

Cell Formulas
Range	Formula
Y2:Y11	Y2	=((Q2+(R2*3))+((T2+(S2*2))))

 

[RattlingCarp3048]

maybe i dont follow - but you are only multiplying 1 value at a time and that adding other - so if a cell is blank - it does not affect others

for example (Q2+R2)*S2 - if S2 is blank - it will multiply by zero, and so give a result of zero
if it was ignored then it would give Q2+R2



i just used
=((Q2+(R2*3))+((T2+(S2*2))))
and it gave all the expected results - see column Y with formula

perhaps i'm missing something

Book2
	Q	R	S	T	U	V	W	X	Y
1	Counted	Missing	Manual	Electronic	Expected Result	vvv calculation being performed
2	4	8	12	20	72	((Q+(R*3))+((T+(S*2))			72
3	4	8	12		52	((Q+(R*3))+(S*2))			52
4	4	8			28	(Q+(R*3)			28
5	4				4	Q			4
6	4	8		20	48	((Q+(R*3))+T)			48
7	4		12	20	48	(Q+((T+(S*2))			48
8		8		20	44	((R*3)+T)			44
9			12	20	44	T+(S*2)			44
10		8	12	20	68	((R*3)+((T+(S*2))			68
11	4			20	24	(Q+T)			24
Sheet1

Cell Formulas
Range	Formula
Y2:Y11	Y2	=((Q2+(R2*3))+((T2+(S2*2))))

correct. if the cells are blank then yes that would work. but mine are not blank, they have a formula in them so i get this...

4	8	12	20	72
4	8	12	-	#VALUE!
4	8	-	-	#VALUE!
4	-	-	-	#VALUE!
4	8	-	20	#VALUE!
4	-	12	20	#VALUE!
-	8	-	20	#VALUE!
-	-	12	20	#VALUE!
-	8	12	20	#VALUE!
4	-	-	20	#VALUE!

 

[Automatrix]

Helper column solution.

Book3
	A	B	C	D	E	F	G	H	I
1	column Q	Column R	Columns S	Column T	Helper R	Helper S
2	(Q)	(R*3)	(S*2)	(T)					<<calculation for each column
3	Counted	Missing	Manual	Electronic			Result	Expected Result	vvv calculation being performed
4	4	8	12	20	24	24	72	72	((Q+(R*3))+((T+(S*2))
5	4	8	12		24	24	52	52	((Q+(R*3))+(S*2))
6	4	8			24	0	28	28	(Q+(R*3)
7	4				0	0	4	4	Q
8	4	8		20	24	0	48	48	((Q+(R*3))+T)
9	4		12	20	0	24	48	48	(Q+((T+(S*2))
10		8		20	24	0	44	44	((R*3)+T)
11			12	20	0	24	44	44	T+(S*2)
12		8	12	20	24	24	68	68	((R*3)+((T+(S*2))
13	4			20	0	0	24	24	(Q+T)
Sheet1

Cell Formulas
Range	Formula
E4:E13	E4	=B4*3
F4:F13	F4	=C4*2
G4:G13	G4	=SUM(A4,D4,E4,F4)

 

[RattlingCarp3048]

Helper column solution.

Book3
	A	B	C	D	E	F	G	H	I
1	column Q	Column R	Columns S	Column T	Helper R	Helper S
2	(Q)	(R*3)	(S*2)	(T)					<<calculation for each column
3	Counted	Missing	Manual	Electronic			Result	Expected Result	vvv calculation being performed
4	4	8	12	20	24	24	72	72	((Q+(R*3))+((T+(S*2))
5	4	8	12		24	24	52	52	((Q+(R*3))+(S*2))
6	4	8			24	0	28	28	(Q+(R*3)
7	4				0	0	4	4	Q
8	4	8		20	24	0	48	48	((Q+(R*3))+T)
9	4		12	20	0	24	48	48	(Q+((T+(S*2))
10		8		20	24	0	44	44	((R*3)+T)
11			12	20	0	24	44	44	T+(S*2)
12		8	12	20	24	24	68	68	((R*3)+((T+(S*2))
13	4			20	0	0	24	24	(Q+T)
Sheet1

Cell Formulas
Range	Formula
E4:E13	E4	=B4*3
F4:F13	F4	=C4*2
G4:G13	G4	=SUM(A4,D4,E4,F4)

I'll try that. Was really hoping for just a single formula rather that a helper column.

 

[Automatrix]

I'll try that. Was really hoping for just a single formula rather that a helper column.

That would certainly be neater. On the other hand, this is much easier to understand if you need to come back to it later. You can also do this in Power Query, where you can erase the helper columns in the final product.

 

[ValuationMan]

The problem is those blanks are actually coming in as text. Using the basic operations (+,-,*,/) will result in errors, but functions can fix it.. I'd recommend something like =SUM(a4,D4)+PRODUCT(B4,3)+PRODUCT(C4,2)

 

[RattlingCarp3048]

The problem is those blanks are actually coming in as text. Using the basic operations (+,-,*,/) will result in errors, but functions can fix it.. I'd recommend something like =SUM(a4,D4)+PRODUCT(B4,3)+PRODUCT(C4,2)

This is close. If the product (in your example B4 or C4) has a value it calculates correctly. But if there is no value to calculate then it returns a 3 or a 2 as a place holder to calculate.

counted	missing	Manual	Electronic	Expected Result	sum(A3,D3)+product(B3,3)+product(C3,2)
4	8	12	20	72	72
4	8	12		52	52
4	8			28	30
4				4	9
4	8		20	48	50
4		12	20	48	51
	8		20	44	46
		12	20	44	47
	8	12	20	68	68
4			20	24	29

 

[ValuationMan]

I wasn't expecting that. This might be a option for columns B and C

=IFERROR(B4*3,0)+IFERROR(C4*2,0)

 

[RattlingCarp3048]

I wasn't expecting that. This might be a option for columns B and C

=IFERROR(B4*3,0)+IFERROR(C4*2,0)

i was actually testing a theory and you beat me to the response. lol your original formulas was pretty darn close. Ive never used the product()function before so with a bit of research google suggested using sumproduct() instead to cancel out the non numeric values and return a 0 in that part of the calculation. this is the final solution. ive tested it every way i can think and applied to live data and everything seems to be checking out.

=sum(A3,D3)+sumproduct(B3,3)+sumproduct(C3,2)

im going to mark your formula as a solution since it pointed me in the direction i needed to be. thanks so much for your help.