COUNTIFS Function not working

Hi

I’m tearing my hair out with this one – can you help?

I have two seperate workbooks – 1 contains a Match ID & a match Outcome “Home Win”, “Draw”, “Away Win”

The 2nd one contains all 243 possible Combinations of 5 Match Outcomes for “Home Win”, “Draw”, “Away Win”.

If I use: –  =COUNTIFS(‘[WIP Footie Predictor.xlsx]Historical Results’!$A:$A,1,'[WIP Footie Predictor.xlsx]Historical Results’!$G:$G,B87) I get the correct answer 80 (there are 80 Matches in Workbook 1 with match ID “1” where the outcome was a “Draw” (B87 in workbook 2)

If I then use: – =COUNTIFS(‘[WIP Footie Predictor.xlsx]Historical Results’!$A:$A,2,'[WIP Footie Predictor.xlsx]Historical Results’!$G:$G,C87) Again, I get the correct answer 152 (there are 152 Matches in Workbook 1 with Match ID “2” where the outcome was a “Home Win” (C87 in Workbook 2)

If i then try to combine the two thus: – =COUNTIFS(‘[WIP Footie Predictor.xlsx]Historical Results’!$A:$A,1,'[WIP Footie Predictor.xlsx]Historical Results’!$G:$G,B87,'[WIP Footie Predictor.xlsx]Historical Results’!$A:$A,2,'[WIP Footie Predictor.xlsx]Historical Results’!$G:$G,C87) I should get (at least) 1 as the answer because I am looking at in Workbook 1 but, it is returning zero?

Logically, this should work because I’m asking it to return a count of Matches where the ID is “1” & “2” & the Outcome for Match 1 is a “Draw” & the Outcome of Match 2 is “Home Win”. My cell ranges from work book 1 are the same size & shape ($A:$A & $G:$G) the only difference being in workbook 2 where cell ref B87 is saying “Draw” & Cell Ref C87 is saying “Home Win”?

Can anybody help me understand why this will not work?

Many thanks

 

Scott.

By: Scott Bennion

Leave a Reply

Your email address will not be published. Required fields are marked *