Home

Excel Countifs

%3CLINGO-SUB%20id%3D%22lingo-sub-309396%22%20slang%3D%22en-US%22%3EExcel%20Countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309396%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20I%20am%20trying%20to%20count%20the%20total%20number%20of%20games%20played%20by%20a%20particulat%20team%20using%20the%20COUNTIFS%20formula%20for%20multiple%20criteria%20over%20different%20ranges.%20Here%20is%20my%20data%20with%20the%20formula%20I%20have%20tried.%20Could%20you%20please%20tell%20me%20what%20I%20have%20got%20wrong%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-309396%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-339107%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-339107%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20find%20the%20attached.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338591%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338591%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20sample%20worksheet%20to%20correctly%20fit%20the%20formula%20on%20your%20data.%3C%2FP%3E%3CP%3EBut%20you%20should%20re-post%20this%20request%20in%20a%20separate%20conversation%2C%20this%20is%20for%20better%20visibility.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338449%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338449%22%20slang%3D%22en-US%22%3E%3CP%3Ehi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20doubt%2C%20I%20need%20to%20get%20the%20count%20of%20Present%26nbsp%3B%22P%22%20from%20the%20criteria%20of%20India%20Operation%20in%20Dubai%20and%20Specific%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEx%3A%20On%202%20feb%26nbsp%3Bno.%20of%20presents%20in%20India%20Operation%20(Dubai).%20Please%20advise%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20in%20advance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-323921%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-323921%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Haytham%2C%20that%20worked.%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESharita%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320298%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320298%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20updated%20the%20workbook%20with%20this%20formula%20instead%3A%3C%2FP%3E%3CPRE%3E%3DSUM(ISTEXT(IF(A7%3D%24A%242%3A%24A%244%2C%24C%242%3A%24H%244%2C0))%2BISTEXT(IF(A7%3D%24B%242%3A%24B%244%2C%24C%242%3A%24H%244%2C0)))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20this%20formula%20is%20an%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FCreate-an-array-formula-E43E12E0-AFC6-4A12-BC7F-48361075954D%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Earray%20formula%3C%2FA%3E%2C%20so%20you%20have%20to%20press%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20to%20enter%20it%20in%20order%20to%20get%20the%20correct%20result%2C%20and%20do%20the%20same%20thing%20each%20time%20you%20enter%20this%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320083%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320083%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Haytham%2C%3C%2FP%3E%3CP%3EThankyou%20for%20your%20reply%20and%20sorry%20for%20my%20delay%20in%20replying%20as%20I%20just%20got%20back.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20formula%2C%20but%20it%20only%20seems%20to%20count%20the%20occurances%20for%20the%20first%20instance%20it%20matches%20the%20criteria.%20So%20for%20example%20if%20I%20had%20Team%20A%20playing%20many%20different%20teams%2C%20it%20only%20counts%20the%20first%20row.%20Is%20there%20a%20way%20for%20it%20to%20carry%20on%20counting%20the%20occurances%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20time.%20I%20much%20appreciate%20it.%3C%2FP%3E%3CP%3ESharita%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309438%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309438%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DSUM(IFERROR(COUNTIF(INDEX(%24C%242%3A%24H%244%2CMATCH(A7%2C%24A%242%3A%24A%244%2C0)%2C)%2C%22%26lt%3B%26gt%3B%22%26amp%3B%22%22)%2C0)%2CIFERROR(COUNTIF(INDEX(%24C%242%3A%24H%244%2CMATCH(A7%2C%24B%242%3A%24B%244%2C0)%2C)%2C%22%26lt%3B%26gt%3B%22%26amp%3B%22%22)%2C0))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F64774iD6786A84CB0A9B98%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Count%20a%20team%20played%20matches.png%22%20title%3D%22Count%20a%20team%20played%20matches.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
rsharita
New Contributor

Hello I am trying to count the total number of games played by a particulat team using the COUNTIFS formula for multiple criteria over different ranges. Here is my data with the formula I have tried. Could you please tell me what I have got wrong? 

7 Replies

Hi,

 

Please try this formula:

=SUM(IFERROR(COUNTIF(INDEX($C$2:$H$4,MATCH(A7,$A$2:$A$4,0),),"<>"&""),0),IFERROR(COUNTIF(INDEX($C$2:$H$4,MATCH(A7,$B$2:$B$4,0),),"<>"&""),0))

Count a team played matches.png

 

Hope that helps

Hi Haytham,

Thankyou for your reply and sorry for my delay in replying as I just got back. 

 

I have tried the formula, but it only seems to count the occurances for the first instance it matches the criteria. So for example if I had Team A playing many different teams, it only counts the first row. Is there a way for it to carry on counting the occurances? 

 

Many thanks for your time. I much appreciate it.

Sharita

Hi,

 

I've updated the workbook with this formula instead:

=SUM(ISTEXT(IF(A7=$A$2:$A$4,$C$2:$H$4,0))+ISTEXT(IF(A7=$B$2:$B$4,$C$2:$H$4,0)))

 

Please note that this formula is an array formula, so you have to press Ctrl+Shift+Enter to enter it in order to get the correct result, and do the same thing each time you enter this formula.

 

Hope that helps

Thank you Haytham, that worked. 

Regards

 

Sharita

hi!

 

I have a doubt, I need to get the count of Present "P" from the criteria of India Operation in Dubai and Specific date. 

 

Ex: On 2 feb no. of presents in India Operation (Dubai). Please advise the formula.

 

thanks in advance. 

Hi,

 

I need a sample worksheet to correctly fit the formula on your data.

But you should re-post this request in a separate conversation, this is for better visibility.

 

Regards

Please find the attached. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies