CODEWITHSUNDEEP
arraysgoogle-sheets
Brian Parker
Brian Parker

Reputation: 3

How to use COUNTIFS based on multiple criteria over different sized ranges?

I have an attendance tracker in which I'm trying to account for each employee type per day.

I have a summary page (sheet1) in which I want the count of each type (A,B,C,D,E) based on the day in cell C2 whether or not they have an X on sheet2 for that day.

Using =COUNTIF(FILTER(Sheet2!$A$5:$GG$969,Sheet2!$A$5:$GG$5=$C$2),"X") I am able to get a total of "X" based on the date in cell C2.

However, I'm having trouble getting the formula to work counting each employee type.

=COUNTIFS(Sheet2!A5:AM31,A7,(FILTER(Sheet2!A5:AM31=C2)),"X")

This gives me an error "Array arguments to COUNTIFS are of different size."

I'm not sure how else to configure this. Below is a link to my sample sheet:

Appreciate any help!

https://docs.google.com/spreadsheets/d/1OdJTwbFsNcR1hO1qzMBGUY4iXcWgDwIAJmBAVE9cs0k/edit?usp=sharing

Upvotes: 0

Views: 125

Answers (2)

player0
player0

Reputation: 1

try:

={"", ""; QUERY(FILTER(Sheet2!A6:A31, FILTER(Sheet2!6:31, Sheet2!5:5=C2)="X"), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''");
 "Total", COUNTA(IFNA(FILTER(Sheet2!A6:A31, FILTER(Sheet2!6:31, Sheet2!5:5=C2)="X")))}

enter image description here

Upvotes: 0

MattKing
MattKing

Reputation: 7783

I think this would work, dragged down for the other groups.

=COUNTIFS(Sheet2!A$6:A$31,A7,FILTER(Sheet2!$6:$31,Sheet2!$5:$5=C$2),"X")

However, I would encourage you to look at this sample sheet (a copy of yours) where I've added a new tab called MK.Help, designed to be hidden, but used as a sort of helper tab to make all sorts of things you might like to do easier. Including the count you asked about.

It has one single formula in cell A2 where you can see what it does.

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(Sheet2!C5:5&"|"&Sheet2!A6:A31&"|"&Sheet2!B6:B31&"|"&Sheet2!C6:31),"|",0,0),"where Col3<>'' order by Col1"))

Upvotes: 1

Related Questions