Sumifs with array criteria?

Question

I have a sheet with two columns: Key and Value.

I'd like to sum all of the Values that has a Key that matches some predefined list of items (e.g,. "Person A, Person B, Person C").

This predefined list of values also in my sheet, in the cells C1:C20.

Ideally, I'd like to do something like: sumifs($ValueColumn, $KeyColumn, C1:C20). I would like this to sum ONLY the Values that has a Key that matches my list.

However, this doesn't seem to work. Any advice?

All you need to do, is to wrap the formula in a SUM and confirm it with control+shift+enter... =SUM(SUMIF(A:A,C1:C20,B:B)) (if in A are the keys and in B are the values) — Dirk Reichel

Ralph Ralph · Accepted Answer · 2016-06-26T19:01:54

The worksheet function SUMIFS is meant to match a range with one condition. Yet, you are trying to match a range with another range. There is not only one key to match but there are several keys (as far as I understand). Hence, you want to sum all of the values if the key can be found in another range.

So, you'll need an array formula to complete this task. The following formula should do just this:

{=SUM(IF(ISERROR(MATCH(C2:C19,A2:A8,0)),0,D2:D19))}

Note, that this is an array formula and thus has to be entered using Ctrl + Shift + Enter. Have a look at the screenshot below and let me know if I interpreted your questions / problem correctly.

Sumifs with array criteria?

2 Answers