# Lost in Excel, looking for formula to look through two range in two rows, whenever there is a number in BOTH in same column multiply them and keep doi

#### userwords

##### New Member
Ive tried with sumproduct not even understanding that it returns booleans. This is my las try:

=SUMPRODUCT(ISNUMBER(\$C13:\$CC13)*ISNUMBER(\$C\$8:\$CC\$8))

All it does is to count TRUES and multiply or something.

I have a row with a formula returning a number C8:CC8 ONLY when the above rows got a value input, when not they show a #N/A

And another row where i manually input number ONLY IN SOME CELLS C13:CC13
when not they are empty no number no formula.

What i need is a formula that will read through both ranges C8:CC8 and C13:CC13

and whenever it finds a number IN BOTH ROWS AT THE SAME COLUMN

multiply them, do that with each pair it finds, then add all of them

so it must ignore when there is a number only in one of the rows in the same column, either because one is empty or because the other has a #N/A

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Welcome to the board.

So the #N/A errors will ONLY appear in row 8, not 13 ?

Try this array formula entered wth CTRL + SHIFT + ENTER
=SUM(IF(ISNUMBER(\$C8:\$CC8),\$C8:\$CC8*\$C13:\$CC13))

Its works thank you. Yep row 8 have some with numbers and some with N/A errors, while row 13 have some with numbers and some empty. And the issue is also that row 8 can have numbers in columns where row 13 is empty in the same column. So only in columns where both row 8 and row 13 have a number it must multiply one for the other, and keep looking for pairs to then add all.

As i was not sure about arrays i did look for doing it with a function and found the sumproduct, but got nothing. I see that using those other functions i usually use like sum and if in a array way are easier in the end.

Unless you see any issue after my last explanation on the requirements, i consider it working.

Replies
6
Views
231
Replies
0
Views
334
Replies
11
Views
3K
Replies
6
Views
436
Replies
1
Views
691

1,203,464
Messages
6,055,571
Members
444,799
Latest member
CraigCrowhurst

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back