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!
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))