# sumproduct that sums multiple non-adjacent columns of data that are numeric and meet a criteria

#### i_excel

##### Board Regular
Hi

I am stumped by the following problem. I would like to calculate the summation of multiple non-adjacent columns of data that meet two conditions. For clarity, I'll refer to the mock dataset below.

A B C D ...
1 2 C 3
N 0 C 1
4 N I 2
3 N C 0
.

The first condition is that the value in Column C should be "C". The second condition is that string data should be treated as equal to 0. The desired formula should then give a value of 10.

Before the issue of string values cropped up, I was using sumproduct(--(C1:C4="C"),A1:A4+B1:B4+D1:D4), which was recommended to me by another user of this site. But for the above problem it returns the value error.

My guess is that this could be readily adapted to exclude string text in specified columns and I'd be interested if anyone knows such a solution. But I'd also be curious to see if some variation on the formula could be used so that specifying these 'culprit' columns wasn't necessary - this is useful for large datasets, for which creating multiple criteria may be arduous.

Kind regards
i_excel

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Excel 2010
ABCD
112C3
2N0C1
34NI2
43NC0
5
610
Sheet1 (2)
Cell Formulas
RangeFormula
A6{=SUM(IFERROR(MMULT(--(\$C\$1:\$C\$4="C"),{1,1,1,1})*\$A\$1:\$D\$4,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you for your time. I have to say, I don't exactly understand what it's doing. But that function is an elegant solution to the problem that I posed.

Do you know, can it easily be extended to a more complex arrangement of data, such as that below, without rearrangement of columns?

A .. J .. AA .. AD ...
1 2 C 3
N 0 C 1
4 N I 2
3 N C 0
.

(Apologies for the wayward formatting)

i_excel

Last edited:
This works, but there has to be a better one:

Excel 2010
112C3
2N0C1
34NI2
43NC0
5
610
Sheet1 (5)
Cell Formulas
RangeFormula
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you again sheetspread. It's a monster as you acknowledge, but I very much appreciate the effort.

i_excel

Yes indeed. You can replace the array of constants with a more dynamic but slower formula segment, neither is great for large data sets:

Excel 2010
112C3
2N0C1
34NI2
43NC0
54BK7
688HJ
7KZNT
836C2
9N1C4
10AA53
1163CT
12JPRU
13
1435
Sheet1 (7)
Cell Formulas
RangeFormula
Press CTRL+SHIFT+ENTER to enter array formulas.

That is fine. I am yet to appreciate what times should be expected with large datasets and complex requests, so if it reduces the chances of human error, I'm more than happy. Thank you again.

i_excel

Last edited:
Hi

With sheetspread's example in post 6, another option:

... confirmed with CSE

Hi pgc01

Thank you for your input. That does seem to be a smart alternative. I can confirm that it works as intended with my data.

Kind regards
i_excel

You're welcome. Thanks for the feedback.

Replies
1
Views
178
Replies
4
Views
175
Replies
10
Views
271
Replies
2
Views
155
Replies
0
Views
134

1,196,447
Messages
6,015,316
Members
441,887
Latest member
acquamarine

### 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