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
Highlighted
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
Highlighted

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

Highlighted

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

Highlighted

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

Highlighted

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. 

Highlighted

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

Highlighted

Please find the attached. 

Related Conversations
Generate Invoice Pricing - Multiple Criteria
TarrahLA in Excel on
0 Replies
Convert text in a cell to number
kaibisnes in Excel on
1 Replies
Userform - Submit button not working
Mr_Buttons in Excel on
2 Replies
IFS with wildcards
itchyfinger in Excel on
3 Replies
Calculating Total hours worked
ChristianMercado in Excel on
5 Replies