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 ...
Formulas and Functions
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