SOLVED

CountIf and Sum(If( - Array help

Copper Contributor

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

@amblee176 

 

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.  

 

2022-09-14_22-29-27.gif

 

Hope this helps.  

Dexter

arrayformula() is formula of google-sheet. You need UNIQUE() with FILTER() function. Share a sample workbook so that we can check.

@Harun24HR

 

This is my spreadsheet.

 

Participant Info.xlsx 

@DexterG_III 

 

Thanks. I've attempted to use the rows formula, not sure I did it right as no response. 

 

Participant Info.xlsx

@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?  

You file is private. Chare sharing permission to view so that we can download.

@DexterG_III 

 

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.

 

https://1drv.ms/x/s!AuRPyfGIeu4MmUgZIA7GUCZRrxiX?e=gOXWnl 

@Harun24HR 

 

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.

 

https://1drv.ms/x/s!AuRPyfGIeu4MmUgZIA7GUCZRrxiX?e=gOXWnl 

@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.   

 

DexterG_III_0-1663251393685.png

 

best response confirmed by amblee176 (Copper Contributor)
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),""))<>""))

Harun24HR_0-1663251835091.png

 

 

Thank you so much! The "=SUM(--(UNIQUE(FILTER(A:A,(B:B=$G$2)*(D:D=$G$1)*(C:C=G6),""))<>"")) worked!

I couldn't make the byrow filters work at all, it had a spill error. I think my real data set might be too large. Thank you!

@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.    

Nice explanation.
1 best response

Accepted Solutions
best response confirmed by amblee176 (Copper Contributor)
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),""))<>""))

Harun24HR_0-1663251835091.png

 

 

View solution in original post