# SUMIFS with multiple criteria evaluated against array?

1. ## SUMIFS with multiple criteria evaluated against array?

Hi guys

Trying to get a "twist" on my calculations here. I have large documents to use on a daily basis, and need to fetch a lot of different sums based on multiple criteria. Most of them are no-brainers, but the last one I'd like to sum based on all the "standard" criterias, but also check if a value in a specific row matches one of the values in a different array.

I've added a document here as an example, but the live data would contain a lot more data (2000+ rows, and "special products" would be 40-50 values). All data is collected in one sheet, the array of special products in a different sheet and all my calculations are done in a third sheet, so any answers will need to work across sheets of course.

Basically what I need is the formula for K3, K4 and K5 in the attached document. I've put in a dummy-explanation in L3, the trick is the last criteria. Goal is to get the total SUM (D-column) from a given Org, to a given Customer type, only from the products listed in H10:H11. As mentioned, this would be an array of 40-50 products in a specific column on a different sheet.

Any takers?

sumifsarray.xlsx

2. ## Re: SUMIFS with multiple criteria evaluated against array?

tRY:

=SUMPRODUCT(SUMIFS(D:D,C:C,"Consumer",A:A,H3,B:B,\$H\$10:\$H\$11))

copied down

3. ## Re: SUMIFS with multiple criteria evaluated against array?

Of course... Thanks!

I have one more which is more tricky, I'll put that in a new thread.

There are currently 1 users browsing this thread. (0 members and 1 guests)