COUNTIFS unpredictably returns Zero-CodePudding

I am trying to grab advertising expenses for the day on the basis of country groups.
The date, and country for the record are in columns I, J and G respectively.

I am doing a VLOOKUP that references a sheet called advertising, with the search key being a date and month together, and picking up the value column based on the country.

Then in order to average it out, I am dividing this lookup, which gives me the ad spend for a set of countries by the number of records for that date, month and country.

=(IF(OR(G4="Spain",G4="Portugal"),VLOOKUP(I4&J4,Advertising!$A$3:$AK,32,0)*IF(COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Spain","Portugal"})>0,1/COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Spain","Portugal"}),0),
IF(G4="France",VLOOKUP(I4&J4,Advertising!$A$3:$AK,33,0)*IF(COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"France"})>0,1/COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"France"}),0),
IF(G4="Italy",VLOOKUP(I4&J4,Advertising!$A$3:$AK,34,0)*IF(COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Italy"})>0,1/COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Italy"}),0),
IF(OR(G4="Belgium",G4="Netherlands"),VLOOKUP(I4&J4,Advertising!$A$3:$AK,35,0)*IF(COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Belgium","Netherlands"})>0,1/COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Belgium","Netherlands"}),0),
IF(G4="Sweden",VLOOKUP(I4&J4,Advertising!$A$3:$AK,36,0)*IF(COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Sweden"})>0,1/COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Sweden"}),0),
IF(G4="United Kingdom",VLOOKUP(I4&J4,Advertising!$A$3:$AK,37,0)*IF(COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"United Kingdom"})>0,1/COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"United Kingdom"}),0),
VLOOKUP(I4&J4,Advertising!$A$3:$AK,31,0)*IF(COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Germany","Austria","Bulgaria","Croatia","Cyprus","Australia","Denmark","Estonia","Finland","Greece","Hungary","Ireland","Latvia","Lithuania","Luxembourg","Malta","Norway","Romania","Russia","Slovakia","Slovenia","Switzerland","UAE"})>0,1/COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Germany","Austria","Bulgaria","Croatia","Cyprus","Australia","Denmark","Estonia","Finland","Greece","Hungary","Ireland","Latvia","Lithuania","Luxembourg","Malta","Norway","Romania","Russia","Slovakia","Slovenia","Switzerland","UAE"}),0)
)))))))

Unfortunately, this is returning an error for me.

As you can see, I have an IF clause to avoid division by zero.

However, I have somehow convinced myself that the error is being reurned in the averaging (i.e. division with the COUNTIFS) process, not in the VLOOKUP. I do believe my COUNTIFS are illegitimately and unexplainably returning zero.

e.g. for row 4 in the main sheet, which I have posted above,

=COUNTIFS(I$4:I,I4,J$4:J,J4,G$4:G,{"Germany","Austria","Bulgaria","Croatia","Cyprus","Australia","Denmark","Estonia","Finland","Greece","Hungary","Ireland","Latvia","Lithuania","Luxembourg","Malta","Norway","Romania","Russia","Slovakia","Slovenia","Switzerland","UAE"})

returns a zero. When I test it out with fewer countries, always including Austria, sometimes it returns zero, sometimes 1.

A sample sheet is at

or like this:

=INDEX(IFERROR(1/(1/(IFNA(VLOOKUP(I4:I&"×"&J4:J, 
 {Advertising!B3:B&"×"&Advertising!C3:C, Advertising!AE3:AK}, 
 MATCH(IFNA(VLOOKUP(G4:G, Sheet3!A:B, 2, )), Advertising!AD1:AK1, 0), ))/
 COUNTIFS(I4:I&"×"&J4:J&IFNA(VLOOKUP(G4:G, Sheet3!A:B, 2, )),   
          I4:I&"×"&J4:J&IFNA(VLOOKUP(G4:G, Sheet3!A:B, 2, )))))))

CodePudding user response：

Use match(), like this:

=arrayformula( 
  iferror( 
    vlookup( 
      I4:I & J4:J, 
      { Advertising!B3:B & Advertising!C3:C, Advertising!B3:AK }, 
      match(G4:G, Advertising!A1:AK1, 0), 
      false 
    ) 
  ) 
)

See the new Solution sheet in your sample spreadsheet. The formula is in cell P4.

Note that not all the country names in your search keys are present in the data.