SUMPRODUCT if Fields Match a RANGE of Criteria

[jtang128]

I'd like to be able to use SUMPRODUCT to sum up a range of cells for which its category corresponds to a list of my criteria.

For example (see attached), I want to sum up all cells in column B with a Category of A, B, or C (which is listed out in a range in column A), resulting in an output of 325 (orange cells). I'd like to be able to add whatever category I want to Column A, so the sumproduct criteria must refer to a range of cells. In other words, I can use OR in the sumproduct to achieve the same effect but I'd rather not because the Criteria lists can get quite large.

sumproduct.png

I believe the formula will look something like:

=SUMPRODUCT(B2:B10,--(C2:C10=??????))

[FlameRetired]

Picture files will not download for many of us. Please upload Excel file of what you describe. It also saves having to retype your data.

[Tony Valko]

I can't see your screencap.

Sounds like you want something like this...

Data Range

	A	B	C	D
1	------	------	------	------
2	A	66	X	273
3	B	86	C
4	C	92	K
5		74	A
6		51	A
7		60	C
8		58	D
9		3	J
10		2	B

This formula entered in D2:

=SUMPRODUCT(--ISNUMBER(MATCH(C2:C10,A2:A4,0)),B2:B10)

[jtang128]

Tony - works perfectly. Thanks!!

[Tony Valko]

You're welcome. Thanks for the feedback!

[jtang128]

As a follow up - can anyone tell me how to modify Tony's formula in his 3/5/15 post above to let it use a 2D (versus a 1D) array as the criteria? i.e. in Tony's post on 3/5 above, if the criteria (i.e. A, B and C) spanned two columns and three rows instead of being only in one column and three rows?

[Tony Valko]

Like this...

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	A	B	66	X	331
3	C	D	86	C
4			92	K
5			74	A
6			51	A
7			60	C
8			58	D
9			3	J
10			2	B

This formula entered in E2:

=SUMPRODUCT(SUMIF(D2:D10,A2:B3,C2:C10))

[jtang128]

Perfect!! Thank you!

[Tony Valko]

You're welcome!

[jtang128]

Another follow up question!

Is it possible to combine the SUMIF method above that Tony posted with additional criteria (i.e. that you would include in the SUMPRODUCT formula using --)

[Tony Valko]

Need more details.

Maybe post a SMALL sample file (about 20 rows of data is plenty) and show us what result you expect.

[jtang128]

So cell G5 has the SUMPRODUCT/SUMIF formula you posted previously. I'd like to be able to add another criteria to the formula to only sum rows where there is a "yes" in column F. So the formula should sum cells in Column D for which column E contains A, B, C, or D AND column F contains a "yes". So the final result should be the 197 in H5.

[Tony Valko]

Like this...

=SUMPRODUCT(SUMIFS(D5:D13,E5:E13,B5:C6,F5:F13,"Yes"))

[jtang128]

Perfect once again. Thank you!

[Tony Valko]

You're welcome!

[lukihnio]

HI,

I have a follow-up question on above thread. I'm trying to use sumproduct across workbooks, one of them will be closed. Trying to sum a column matching,certain keyword list. Actually, these keywords,
should be an exclusion. In other words, i would like to sum column with rows which do not include the list of keywords. I would like to maintain keyword list in a column, separately, which will be
updated from time to time. But I do not want to update rows of sumproduct formulas, every time there is a new keyword.
I'm not very proficient with VBA, but I would consider VBA code as an option as well.
Any help is much appreciated.

here is the formula I have:
=SUMPRODUCT(--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$E$2:$E$500=A89),--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$A$2:$A$500=B89),--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500<>"Fee"),--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500<>"Subscription Fee"),--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500<>"Boat Fee"),--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500<>"Other"),--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500<>"Travel"),--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500<>"Serving"),'[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$J$2:$J$500)



But here is what ideally I would like to have:
=SUMPRODUCT(--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$E$2:$E$500=A89),--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$A$2:$A$500=B89),--('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500<>Keywrods),'[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$J$2:$J$500)

List of keywords would be in separate column:
Exclusion keywords:
Fee
Subscription Fee
Boat Fee
Other
Travel
serving


I hope I did not violate any forum rules. If I did, please accept my apologies. Please point out a violation and I'll do my best to comply with the forum policy.

[Tony Valko]

The forum owner prefers that you start your own thread.

Forum rule #2