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

     

     

amblee176's avatar
amblee176
Copper Contributor
Sep 15, 2022

Harun24HR

 

This is my spreadsheet.

 

https://aleragroup-my.sharepoint.com/:x:/r/personal/aanya_lee_aleragroup_com/Documents/Participant%20Info.xlsx?d=w3c2bef3add364484811035fbe6b4293a&csf=1&web=1&e=oREVDz 

Harun24HR's avatar
Harun24HR
Bronze Contributor
Sep 15, 2022
You file is private. Chare sharing permission to view so that we can download.
  • amblee176's avatar
    amblee176
    Copper Contributor
    Sep 15, 2022

    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 

    • Harun24HR's avatar
      Harun24HR
      Bronze Contributor
      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),""))<>""))

       

       

      • amblee176's avatar
        amblee176
        Copper Contributor
        Sep 15, 2022
        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!

Resources