# Sumifs - two criteria where one criteria is to match anything in a range

1. ## Sumifs - two criteria where one criteria is to match anything in a range

I want to sum a column, where BOTH another column = a value in one cell, AND yet another column equals anything from a defined range.

EG:

 A B C D E Customer Accounting code Value 1 ABC 3000 \$400 2 ABC 4037 \$35 3 DEF 3000 \$5674 4 ABC 3050 \$500 5 6 ABC 7 8 3000 9 3050 10 3100 11

I want something like =sumifs(C1:C4;A1:A4;"="&A6;B1:B4;"="&{A8:A10})
The answer to this example should be \$900

But it doesn't work with a range as the criteria.

I've seen
=SUM(SUMIFS(C1:C4;B1:B4;{"3000","3050"}))
and
=SUMPRODUCT(--ISNUMBER(SEARCH(A1:A4;A6)))>0
and
=SUM(IF(ISERROR(MATCH(B1:B4;A8:A10;0))=FALSE;C1:C4;0))

But none of these allow for the (changeable) range as one criteria AND a second criteria.

Any suggestions?
Thanks

2. ## Re: Sumifs - two criteria where one criteria is to match anything in a range

Maybe this...

=SUMPRODUCT(SUMIFS(C1:C4;A1:A4;A6;B1:B4;A8:A10))

3. ## Re: Sumifs - two criteria where one criteria is to match anything in a range

Nope. Still getting zero. And it doesn't work with =SUMPRODUCT(SUMIFS(C1:C4;B1:B4;A8:A10)) either.

4. ## Re: Sumifs - two criteria where one criteria is to match anything in a range

Works for me.

Here's a sample file...

5. ## Re: Sumifs - two criteria where one criteria is to match anything in a range

Great. Thanks!

Do you know if there's any way to make this work in Google Sheets?

6. ## Re: Sumifs - two criteria where one criteria is to match anything in a range

Originally Posted by FHen
Do you know if there's any way to make this work in Google Sheets?

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