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

  • DexterG_III's avatar
    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's avatar
      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 

      • DexterG_III's avatar
        DexterG_III
        Iron Contributor
        Sep 15, 2022

        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.   

         

         

Resources