Forum Discussion

O_edwardsKPPB-9's avatar
O_edwardsKPPB-9
Copper Contributor
May 22, 2019

Count and Sum between Options

Hi All,

 

I have a sheet with 2 columns, one for Country and one to represent a status, either status A or status B.  I would like to have a formula that can sort through and give me the number of A statuses and the number of B statuses per country. So far, I have been unable to come up with anything.  Any ideas?

 

Thanks!

-O

Formulas and Functions

4 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 22, 2019
    Use COUNTIFS like this:
    =COUNTIFS(Countries,Country,
    Statuses,Status)
    • O_edwardsKPPB-9's avatar
      O_edwardsKPPB-9
      Copper Contributor
      May 22, 2019

      Hi All,

       

      Thanks for the replies.  I am having some issues with the Countif function.  Attached below is an example of a chart we get (though they are usually about 200 rows!).  Would the countif function be able to tell me BE has 1 issues, CH has 1 allowed and 1 issued, etc.

      COUNTRYSTATUS
      BEIssued
      CHAllowed
      CHIssued
      DEIssued
      EPIssued
      JMPending
      ESIssued

      Twifoo 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 22, 2019

        O_edwardsKPPB-9 , for this simple model

        you may build PivotTable (to the right) with couple of clicks.

         

        For the formula you first to build the list of all countries and list of all issues (left column and top row in the range at bottom), after that in top left column

        =COUNTIFS($A$2:$A$8,$D11,$B$2:$B$8,E$10)

        and drag it down and to the right.

Resources