Forum Discussion
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. 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))
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 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
