Forum Discussion

Copper Contributor

Jan 06, 2019

Excel Countifs

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

Haytham Amairah

Silver Contributor

Jan 06, 2019

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

Hope that helps

Count a team played matches.xlsx10 KB

rsharita
Copper Contributor
Jan 17, 2019
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
Count a team played matches (1).xlsx10 KB
- Haytham Amairah
  Silver Contributor
  Jan 17, 2019
  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 https://support.office.com/en-us/article/Create-an-array-formula-E43E12E0-AFC6-4A12-BC7F-48361075954D, 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
  Count a team played matches (Solved).xlsx11 KB
  - vigneshasokan7
    Copper Contributor
    Feb 13, 2019
    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.

Resources