Forum Discussion
CountIf and Sum(If( - Array help
I am attempting to count the number of unique participants that participated in submitting information upon their location, size and job code they are submitting. I have attempted sum(if( functions with countif in array formats, but am getting a zero response when I know there are participants.
I have Microsoft 365, using Excel. Not sure how to upload excel file.
For my WA, Large - Job Code 1
The result should be: 1
=arrayformula(SUM(IF(($G$2=$A$2:$A$251)*($A$2:$A$251=$G$1)*($A$2:$A$251>$G8),1/COUNTIFS($B$2:$B$251,$G$2=$B$2:$B$251,$D$2:$D$251,$D$2:$D$251=$G$1,$C$2:$C$251,$C$2:$C$251>$G8)),0)
Columns are:
Participants (4 digit numbers)
States (WA, OR)
Size (Small, Medium, Large)
Job code (numbers, up to 180)
13 Replies
- arrayformula() is formula of google-sheet. You need UNIQUE() with FILTER() function. Share a sample workbook so that we can check.
Here is a much easier method:
=ROWS(FILTER(Table4[Participants],(Table4[States]="OR")*(Table4[Size]="Small")*(Table4[Job Code]<90)))To demonstrate, the formula under the Array heading shows the actual participant ID's that match all other criteria. While the formula under the Summarized header is that pasted above and simply counts the number of rows returned by the filter function.
Hope this helps.
Dexter
Thanks. I've attempted to use the rows formula, not sure I did it right as no response.
https://aleragroup-my.sharepoint.com/:x:/r/personal/aanya_lee_aleragroup_com/Documents/Participant%20Info.xlsx?d=w3c2bef3add364484811035fbe6b4293a&csf=1&web=1&e=oREVDz
amblee176 it appears I'm not able to access files within your organization. On the share settings before you copy the link, do you have it set to anyone with link can view/edit?
