adding the product of various pairs of cells...

[racingsnake2000]

Hello Again...
After being helped so quickly with my last question, I thought I'd post again!
This time, I'd like to multiply adjacent cells in a row in pairs only and then add the solutions of each pair to achieve a total.
The problem I have is this:
Some of the pairs have no values in them, and the others do....
What happens in the answer column is that I end up getting the #VALUE! answer.
How do I get my formula to ignore the pairs(or individual cells) with no values in them to achieve the answer?
In each row, there are 26 pairs of cells that need to be multiplied and the 26 solutions need to be added together. There are however only a random single pair of cells with values in them in each of the 6848 rows...
Thanks again for your time!

 

[p45cal]

What's your current formula?

 

[racingsnake2000]

Don't laugh!

=SUM(AS4*AT4)+SUM(AU4*AV4)+SUM(AW4*AX4)+SUM(AY4*AZ4)+SUM(BA4*BB4)+SUM(BC4*BD4)+SUM(BE4*BF4)+SUM(BG4*BH4)+SUM(BI4*BJ4)+SUM(BK4*BL4)+SUM(BM4*BN4)+SUM(BO4*BP4)+SUM(BQ4*BR4)+SUM(BS4*BT4)+SUM(BU4*BV4)+SUM(BW4*BX4)+SUM(BY4*BZ4)+SUM(CA4*CB4)+SUM(CC4*CD4)+SUM(CE4*CF4)+SUM(CG4*CH4)+SUM(CI4*CJ4)+SUM(CK4*CL4)+SUM(CM4*CN4)+SUM(CO4*CP4)+SUM(CQ4*CR4)

I'm self taught and nearly failed maths in school, so it takes a little while to pick things up

 

[T. Valko]

Hello Again...
After being helped so quickly with my last question, I thought I'd post again!
This time, I'd like to multiply adjacent cells in a row in pairs only and then add the solutions of each pair to achieve a total.
The problem I have is this:
Some of the pairs have no values in them, and the others do....
What happens in the answer column is that I end up getting the #VALUE! answer.
How do I get my formula to ignore the pairs(or individual cells) with no values in them to achieve the answer?
In each row, there are 26 pairs of cells that need to be multiplied and the 26 solutions need to be added together. There are however only a random single pair of cells with values in them in each of the 6848 rows...
Thanks again for your time!

Is this what you want:

Book1
	A	B
2	3	5
3	2	_
4	_	6
5	10	2
6	_	_
7	_	_
8	10	3
9	5	2
10	2	7
Sheet1

=SUMPRODUCT(A2:A10,B2:B10)

Returns 89

Empty cells are evaluated as 0 and 0*N = 0 and SUM of 0 has no effect on the final total.

 

[racingsnake2000]

Not really...
I have 52 cells adjacent to each other in say row 5.
I' like to multiply for example A5*B5+C5*D5+E5*F5+G5*H5 etc...
All up there are 26 pairs of cells that need to be multiplied as pairs and then the solutions to each of those 26 equations need to be added together to provide an answer to the horizontal row.

Does that make sense?

 

[p45cal]

I couldn't get the #value with empty cells with your formula (in AQ4 below) except by putting some text in a cell (see below). However the formula in AP4 (which must be entered using CTRL+SHIFT+ENTER - not just with ENTER) seems to be immune from text and blanks.
I haven't shown the whole range, but it goes up by 1 until column CR.
It gives the same answers as your formula.

Excel Workbook
	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
4	24090	#VALUE!		1	2	aa	4	5	6	7
Sheet12

 

[T. Valko]

Not really...
I have 52 cells adjacent to each other in say row 5.
I' like to multiply for example A5*B5+C5*D5+E5*F5+G5*H5 etc...
All up there are 26 pairs of cells that need to be multiplied as pairs and then the solutions to each of those 26 equations need to be added together to provide an answer to the horizontal row.

Does that make sense?

Try this array formula**:

=SUMPRODUCT(IF(MOD(COLUMN(A5:G5)-COLUMN(A5),2)=0,A5:G5),IF(MOD(COLUMN(B5:H5)-COLUMN(A5),2)=1,B5:H5))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Note how the ranges are offset.

Also, this formula is robust against new column insertions to the left of the referenced range. If you know for certain that you will never need to insert new columns to the left of the referenced range then we can use this slightly shorter less complicated version.

Still array entered**:

=SUMPRODUCT(IF(MOD(COLUMN(A5:G5),2)=1,A5:G5),IF(MOD(COLUMN(B5:H5),2)=0,B5:H5))

 

[racingsnake2000]

Mate,
You are a legend!
Works a treat

 

[T. Valko]

{=SUMPRODUCT(IF(ISODD(COLUMN(AS4:CQ4)),AS4:CQ4),IF(ISEVEN(COLUMN(AT4:CR4)),AT4:CR4))}

Note that that formula will only work in Excel versions 2007 and later.