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

     

     

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

        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.    

Resources