Forum Discussion

amblee176's avatar
amblee176
Copper Contributor
Sep 15, 2022
Solved

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 w...
excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Sep 15, 2022

    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),""))<>""))

     

     

DexterG_III's avatar
DexterG_III
Iron Contributor
Sep 15, 2022

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.  

 

 

Hope this helps.  

Dexter

amblee176's avatar
amblee176
Copper Contributor
Sep 15, 2022

DexterG_III 

 

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

Resources