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
Bookmarks