Home

Count and Sum between Options

%3CLINGO-SUB%20id%3D%22lingo-sub-634613%22%20slang%3D%22en-US%22%3ECount%20and%20Sum%20between%20Options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-634613%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20sheet%20with%202%20columns%2C%20one%20for%20Country%20and%20one%20to%20represent%20a%20status%2C%20either%20status%20A%20or%20status%20B.%26nbsp%3B%20I%20would%20like%20to%20have%20a%20formula%20that%20can%20sort%20through%20and%20give%20me%20the%20number%20of%20A%20statuses%20and%20the%20number%20of%20B%20statuses%20per%20country.%20So%20far%2C%20I%20have%20been%20unable%20to%20come%20up%20with%20anything.%26nbsp%3B%20Any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E-O%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-634613%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-635686%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20and%20Sum%20between%20Options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-635686%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F346872%22%20target%3D%22_blank%22%3E%40O_edwardsKPPB-9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20a%20pivot%20table.%3C%2FP%3E%3CP%3ECountry%20in%20rows%20area.%3C%2FP%3E%3CP%3EStatus%20in%20rows%20area%20(or%20columns%20area)%20and%20in%20value%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-635769%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20and%20Sum%20between%20Options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-635769%22%20slang%3D%22en-US%22%3EUse%20COUNTIFS%20like%20this%3A%3CBR%20%2F%3E%3DCOUNTIFS(Countries%2CCountry%2C%3CBR%20%2F%3EStatuses%2CStatus)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-635837%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20and%20Sum%20between%20Options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-635837%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20replies.%26nbsp%3B%20I%20am%20having%20some%20issues%20with%20the%20Countif%20function.%26nbsp%3B%20Attached%20below%20is%20an%20example%20of%20a%20chart%20we%20get%20(though%20they%20are%20usually%20about%20200%20rows!).%26nbsp%3B%20Would%20the%20countif%20function%20be%20able%20to%20tell%20me%20BE%20has%201%20issues%2C%20CH%20has%201%20allowed%20and%201%20issued%2C%20etc.%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECOUNTRY%3C%2FTD%3E%3CTD%3ESTATUS%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBE%3C%2FTD%3E%3CTD%3EIssued%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECH%3C%2FTD%3E%3CTD%3EAllowed%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECH%3C%2FTD%3E%3CTD%3EIssued%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDE%3C%2FTD%3E%3CTD%3EIssued%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEP%3C%2FTD%3E%3CTD%3EIssued%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJM%3C%2FTD%3E%3CTD%3EPending%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EES%3C%2FTD%3E%3CTD%3EIssued%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-635912%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20and%20Sum%20between%20Options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-635912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F346872%22%20target%3D%22_blank%22%3E%40O_edwardsKPPB-9%3C%2FA%3E%26nbsp%3B%2C%20for%20this%20simple%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20491px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F115735iCB949BEACB054C47%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20build%20PivotTable%20(to%20the%20right)%20with%20couple%20of%20clicks.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20formula%20you%20first%20to%20build%20the%20list%20of%20all%20countries%20and%20list%20of%20all%20issues%20(left%20column%20and%20top%20row%20in%20the%20range%20at%20bottom)%2C%20after%20that%20in%20top%20left%20column%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIFS(%24A%242%3A%24A%248%2C%24D11%2C%24B%242%3A%24B%248%2CE%2410)%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%20and%20to%20the%20right.%3C%2FP%3E%3C%2FLINGO-BODY%3E
O_edwardsKPPB-9
New Contributor

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

@O_edwardsKPPB-9 

 

Create a pivot table.

Country in rows area.

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

 

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

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 

@O_edwardsKPPB-9 , for this simple model

image.png

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