• 511K Members
• 4,466 Online
• 608K Conversations

New Contributor

# 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

4 Replies

# Re: Count and Sum between Options

Create a pivot table.

Country in rows area.

Status in rows area (or columns area) and in value area.

# Re: Count and Sum between Options

Use COUNTIFS like this:
=COUNTIFS(Countries,Country,
Statuses,Status)

# Re: Count and Sum between Options

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.

 COUNTRY STATUS BE Issued CH Allowed CH Issued DE Issued EP Issued JM Pending ES Issued

# Re: Count and Sum between Options

@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.

Related Conversations
How to count multiple values in a cell
Ugarte335 in Excel on
7 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
Add Column Using Count
Tim Hunter in SQL Server on
0 Replies