• 408K Members
• 8,546 Online
• 463K 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
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies