COUNTIFs giving SPILL Error

[dfelock]

Hello All,

I'm having an issue with COUNTIFS. Here's my situation. I'm building an attendance tracker and I have all of my reports listed as well as the work week:

I currently have a COUNTIF formula: COUNTIF(DC$17,"X")+COUNTIF(DC$18,"X")+COUNTIF(DC$19,"X")+COUNTIF(DC$10,"X")+COUNTIF(DC$11,"X"). I use this with a lower section that lets me know if I have all my work stations covered for the day. Now though, as per the picture, when I try to turn it into a COUNTIFS(DC$5,@{"X","WFH"},DC$9,@{"X","WFH"},DC$11,@{"X","WFH"},DC$14,@{"X","WFH"}) I'm getting a SPILL error. Any ideas?

 

[RoryA]

If you use an array for the criteria, you will get an array of results, not just one.

Out of interest, what do you think that formula is doing?

 

[dfelock]

If you use an array for the criteria, you will get an array of results, not just one.

Out of interest, what do you think that formula is doing?

So here is my thought process:
The COUNTIFS will count if each cell contains either "X" or "WFH"

Above is a screenshot of the cells that I have and then lower section that adds it all up, or is supposed to. Below is the original formula before adding in the WFH part.
="ET02: " & COUNTIF(DD$10,"X")+COUNTIF(DD$8,"X")+COUNTIF(DD$11,"X")+COUNTIF(DD$6,"X")+COUNTIF(DD$14,"X")

Dan

 

[RoryA]

That's what I suspected, but is not what that formula does. It will return an array of two values, each of them will be 1 if all the cells contain "X" or if all the cells contain "WFH", but 0 for any other combination. I think you want:

="ET02: " & SUM(COUNTIF(DD$10,{"X","WFH"}),COUNTIF(DD$8,{"X","WFH"}),COUNTIF(DD$11,{"X","WFH"}),COUNTIF(DD$6,{"X","WFH"}),COUNTIF(DD$14,{"X","WFH"}))

 

[dfelock]

That's what I suspected, but is not what that formula does. It will return an array of two values, each of them will be 1 if all the cells contain "X" or if all the cells contain "WFH", but 0 for any other combination. I think you want:

="ET02: " & SUM(COUNTIF(DD$10,{"X","WFH"}),COUNTIF(DD$8,{"X","WFH"}),COUNTIF(DD$11,{"X","WFH"}),COUNTIF(DD$6,{"X","WFH"}),COUNTIF(DD$14,{"X","WFH"}))

You are amazing!!! Thank you! Worked perfectly.

 

[RoryA]

Glad we could help.