Looking for a simplified SUMPRODUCT solution

[dwgnome]

Hi,

I have one column with code letters such as T, S, P, O, the other column values. I want to sum the values when a corresponding matching letter is present.

=SUMPRODUCT(--(CAT="T"),DV12:DV74)

Works, but

=SUMPRODUCT(--(CAT="T"),--(CAT="O"),DV12:DV74)

does not.

I even tried:

=SUMPRODUCT(--(ISNUMBER(SEARCH(CAT,"T"))),--(ISNUMBER(SEARCH(CAT,"O"))),DV12:DV74)

what I really want to do is to not have to add two similar expressions but do something like this:

=SUMPRODUCT(--(CAT={"T","O"}),DV12:DV74)

Thanks.

 

[sheetspread]

A cell can't equal O and T at the same time, use + for OR

 

[West Man]

=SUMPRODUCT(--(CAT="T"),--(CAT="O"),DV12:DV74) does not work because those cells in CAT that are equal to T are not equal to O. Consequently the result will always be zero. Try (untested):

=SUMPRODUCT(CAT="T")+(CAT="O"),DV12:DV74)

 

[sheetspread]

Excel Workbook
	C	D
2	T	1
3	S	3
4	P	5
5	O	7
6	P	4
7	T	3
8	S	6
9	S	9
10	O	9
11	P	4
12
13		20
Sheet2

 

[dwgnome]

Thank you sheetspread and west man. I guess trying to use an array within SUMPRODUCT doesn't work. However, I see that the following formula does what I envision:

=[B][COLOR=#0000ff]SUM(IF(E12:E74={"O","S"},DY12:DY74))[/COLOR][/B]

However, it is a array formula (CSE). I was hoping I can do the {"O","S"} bit within a non-array like SUMPRODUCT.

 

[Aladin Akyurek]

Thank you sheetspread and west man. I guess trying to use an array within SUMPRODUCT doesn't work. However, I see that the following formula does what I envision:

=[B][COLOR=#0000ff]SUM(IF(E12:E74={"O","S"},DY12:DY74))[/COLOR][/B]

However, it is a array formula (CSE). I was hoping I can do the {"O","S"} bit within a non-array like SUMPRODUCT.

1) Just enter:

=SUMPRODUCT(--ISNUMBER(MATCH(E12:E74,{"O","S"},0)),DY12:DY74)

2) Just enter:

=SUM(SUMIF(E12:E74,{"O","S"},DY12:DY74)

3) CSE...

=SUM(IF(ISNUMBER(MATCH(E12:E74,{"O","S"},0)),DY12:DY74))

Note 1. If the array constant is admissible (no need for a range-based list), option (2) is the best thing to do.

Note 2. While a SumProduct formula does not require CSE, it is just as much an array-processing formula.

 

[dwgnome]

Thank you Aladin, your explanation and input is just what I was looking for.

 

[Aladin Akyurek]

Thank you Aladin, your explanation and input is just what I was looking for.

You are welcome. Thanks for providing feedback.