Sep 14 2022 10:03 PM
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)
Sep 14 2022 10:31 PM
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
Sep 15 2022 12:07 AM
Sep 15 2022 06:57 AM
Sep 15 2022 06:58 AM
Thanks. I've attempted to use the rows formula, not sure I did it right as no response.
Sep 15 2022 07:01 AM
@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?
Sep 15 2022 07:04 AM
Sep 15 2022 07:10 AM
I had to move to my personal outlook/microsoft account. I'll have to move between the two, but it isn't a full microsoft 365.
Sep 15 2022 07:10 AM
I had to move to my personal outlook/microsoft account. I'll have to move between the two, but it isn't a full microsoft 365.
Sep 15 2022 07:18 AM
@amblee176 if you highlight one of the lookup references in your formula and press F9, it will return the array within the formula bar as shown. If you notice, there is a space after every Small in column C. When I add that space to cell G6, the formula works correctly. You can use trim or clean as a temporary formula next to your table to remove the extra space, and then copy that data and past as values over the original.
Sep 15 2022 07:24 AM
Solution@amblee176 You can try any of below
=BYROW(G6:G8,LAMBDA(x,SUM(--(UNIQUE(FILTER(A:A,(B:B=$G$2)*(D:D=$G$1)*(C:C=x),""))<>""))))
=SUM(--(UNIQUE(FILTER(A:A,(B:B=$G$2)*(D:D=$G$1)*(C:C=G6),""))<>""))
Sep 15 2022 07:53 AM
Sep 15 2022 08:07 AM
@amblee176 Glad you figured it out. For your future reference, unlike the old version of excel which would generally return only one result in one cell, the new dynamic array functions can return many results in multiple cells. This behavior is called "spilling", where the formula resides in only one cell, but the results spill to as many rows and columns equal to the number of results returned in the array.
When you see SPILL, it means there is data in the range generally below or to the right of the formula, which interferes with its ability to spill into the required cells.
Long story short, the spill error actually says the formula is working. :) You just need to clear some cells near it.
Sep 15 2022 07:24 AM
Solution@amblee176 You can try any of below
=BYROW(G6:G8,LAMBDA(x,SUM(--(UNIQUE(FILTER(A:A,(B:B=$G$2)*(D:D=$G$1)*(C:C=x),""))<>""))))
=SUM(--(UNIQUE(FILTER(A:A,(B:B=$G$2)*(D:D=$G$1)*(C:C=G6),""))<>""))