CODEWITHSUNDEEP
excelsumifs
whoisj
whoisj

Reputation: 388

Excel SUMIFS with Ranges as Criteria

I'm having some issues getting SUMIFS to do what I want (maybe because it cannot). Here's exactly what I'm trying to setup:

Basically I'm trying to look at how the data on my various data servers is bucketed. So I have a list of extensions that I care about (let's say .docx, .xlsx, and .pptx) and a list of servers I care about (let's say serv_a, serv_b, and serv_c). Given the massive worksheet I have with all the data from all the servers and all the files I need to get the count of files on the server which have extensions that I care about.

Here's the formula I'm using now (it is not working)

  =SUMIFS(data!$C:$C, data!$A:$A, A2, data!$B:$B, filter!$A:$A)

Where data!$C is the count of a extension on a server, data!$A is the server name, A2 is server name I want to filter on, and filter!$A is the list of extensions I want to filter on.

I do think it's the LIST of extensions to filter by which is breaking things. Given a single value (.docx) I can count 143,567 files on a given server, but the query returns 1.

Thanks!

Upvotes: 1

Views: 17943

Answers (1)

L42
L42

Reputation: 19737

If you enter a range of cell as criteria, you'll have to enter it as array formula (see link).
You do that by pressing Ctrl+Shift+Enter.
Your formula should then look like below:

{=SUMIFS(data!$C:$C, data!$A:$A, A2, data!$B:$B, filter!$A:$A)}

That will then return an array of values which are the SUMIFS result of each criteria.
To return it into a single value, sum it up using SUM which accepts array parameter.
Final formula should look like below, again entered using Ctrl+Shift+Enter. HTH.

{=SUM(SUMIFS(data!$C:$C, data!$A:$A, A2, data!$B:$B, filter!$A:$A))}

Bonus: Credits to barry houdini

This non-array formula works the same way:

=SUMPRODUCT(SUMIFS(data!$C:$C, data!$A:$A, A2, data!$B:$B, filter!$A:$A))

Upvotes: 2

Related Questions