Forum Discussion

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

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

Book1MS-Tech.xlsx15 KB

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
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
- DexterG_III
  Iron Contributor
  Sep 15, 2022
  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?
  - amblee176
    Copper Contributor
    Sep 15, 2022
    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