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)

 

[ValuationMan]

You're welcome! Good find on sumproduct. One of my favorite functions has another use. I believe you could simplify further by doing this:

=SUM(A3:D3)+SUMPRDOUCT(B3:C3,{3,2})

 

[RattlingCarp3048]

You're welcome! Good find on sumproduct. One of my favorite functions has another use. I believe you could simplify further by doing this:

=SUM(A3:D3)+SUMPRDOUCT(B3:C3,{3,2})

that did not work. the calculations came out much higher than they should have. ill be ok with the other formula. its no big deal. its already much simpler than my original direction.

this has struck my curiosity though (if i need a new thread ill be happy to make one) but what if i want to go the other way, divide or subtract and ignore non numeric values instead of multiply?

for example: =(AD3/2) + AE3 + ((AF3 - AE3) * 0.1) right now i have several nested if's just to ignore the non numeric values. its similar circumstances to this original post, just a little different.

 

[ValuationMan]

I would think you could use SUMPRODUCT(array_1,1/array_2)