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

[i_excel]

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

 

[sheetspread]

Excel 2010
	A	B	C	D
1	1	2	C	3
2	N	0	C	1
3	4	N	I	2
4	3	N	C	0
5
6	10
Sheet1 (2)

Cell Formulas
Range	Formula
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.

 

[i_excel]

Hi sheetspread

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

 

[sheetspread]

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

Excel 2010
	A	J	AA	AD
1	1	2	C	3
2	N	0	C	1
3	4	N	I	2
4	3	N	C	0
5
6	10
Sheet1 (5)

Cell Formulas
Range	Formula
A6	{=SUM(IFERROR(MMULT(--($AA$1:$AA$4="C"),{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})*--ISNUMBER(MATCH(COLUMN($A$1:$AD$4),{1,10,27,30},0))*$A$1:$AD$4,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[i_excel]

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

i_excel

 

[sheetspread]

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
	A	J	AA	AD
1	1	2	C	3
2	N	0	C	1
3	4	N	I	2
4	3	N	C	0
5	4	B	K	7
6	8	8	H	J
7	K	Z	N	T
8	3	6	C	2
9	N	1	C	4
10	A	A	5	3
11	6	3	C	T
12	J	P	R	U
13
14	35
Sheet1 (7)

Cell Formulas
Range	Formula
A14	{=SUM(IFERROR(MMULT(--($AA$1:$AA$12="C"),(COLUMN($A$1:$AD$12)^0))*(ISNUMBER(MATCH(COLUMN($A$1:$AD$12),{1,10,27,30},0))+0)*$A$1:$AD$12,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[i_excel]

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

 

[pgc01]

Hi

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

=SUM(IF(AA1:AA12="C",IF(ISNUMBER(MATCH(COLUMN(A:AD),{1,10,27,30},0)),A1:AD12)))

... confirmed with CSE

 

[i_excel]

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

 

[pgc01]

You're welcome. Thanks for the feedback.