Forum Discussion

ChrisHH's avatar
ChrisHH
Copper Contributor
Jan 24, 2024

EXCEL Countifs

IF range A2:A147,="y"  Count the number of times in Range N2:N147,=City1, City1. City 3 City 4, City 5, City 6 appears?  I am having trouble with counting the multiple ranges and criteria?? 
excel
Harun24HR's avatar
Harun24HR
Bronze Contributor
Jan 24, 2024
Share a excel workbook and show your desired output.
  • ChrisHH's avatar
    ChrisHH
    Copper Contributor
    Jan 24, 2024

    Harun24HR 

     

     

    So I am looking at A2:A147 if there is a "y" I want to count the city to come up how many from a city are coming - so this is easy Countif works fine for counting just one city. 

    Result - Row 2 and 3 have a "y" so my count for Red Deer s/b 2 

    The issue is when I have a few or more 5 cities and I am want to add the count for those coming together. The hidden issue maybe this will will not work as the range1 has blanks cells.  

    Result if I want to count if I combine Countifs several cities so if I want to group Red Deer and Lacombe and a few other cities COUNTIFS result is coming back 0 I think because of the syntax or it sees range 1 id blank and comes back with zero for the value. 

    • Harun24HR's avatar
      Harun24HR
      Bronze Contributor
      Jan 24, 2024

      ChrisHH Lets try following formulas-

      =COUNTIFS($A$2:$A$8,"y",$D$2:$D$8,D2)

      Group by cities.

      =HSTACK(UNIQUE(D2:D8),MAP(UNIQUE(D2:D8),LAMBDA(x,COUNTIFS(A2:A8,"y",D2:D8,x))))

       

       

Resources