COUNTIFS value error

%3CLINGO-SUB%20id%3D%22lingo-sub-2861308%22%20slang%3D%22en-US%22%3ECOUNTIFS%20value%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2861308%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20getting%20a%20value%20error%20with%20the%20countifs%20function%20referencing%20more%20than%20one%20criteria%20in%20another%20worksheet%20within%20the%20same%20workbook.%20The%20countif%20function%20works%20at%20the%20top%20level%20(age)%2C%20but%20when%20I%20want%20to%20add%20age%20and%20group%20(e.g.%2C%20CTL%20or%20MDD)%20as%20a%20second%20criteria%20the%20countifs%20function%20does%20not%20work.%20Attached%20a%20screenshot.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2861308%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2862476%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20value%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2862476%22%20slang%3D%22en-US%22%3EWith%20your%20permission%2C%20if%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%20Even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%20You%20could%20get%20a%20precise%20solution%20much%20faster%20with%20a%20file%20(w%2Fout%20sensitive%20data).%20This%20would%20also%20be%20a%20blessing%20for%20all%20of%20us%2C%20as%20we%20can%20understand%20the%20problem%20much%20better%2C%20a%20win-win%20situation%20for%20everyone.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20your%20understanding%20and%20patience%3CBR%20%2F%3E%3CBR%20%2F%3ENikolinoDE%3CBR%20%2F%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2862526%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20value%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2862526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%26nbsp%3BI%20attached%20it%20here.%20For%20reference%2C%20the%20formulas%20in%20the%20participant%20tracking%20sheet%20tables%20pull%20from%20labels%20in%20cells%20A543-566.%20I'd%20like%20the%20demographics%20diagram%20sheet%20to%20pull%20from%20the%20participant%20tracking%20sheet.%20Again%2C%20the%20top%20level%20(age)%20pulls%20successfully%2C%20but%20not%20when%20I%20add%20age%20AND%20another%20demographic%20and%20so%20on%20and%20so%20forth.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm getting a value error with the countifs function referencing more than one criteria in another worksheet within the same workbook. The countif function works at the top level (age), but when I want to add age and group (e.g., CTL or MDD) as a second criteria the countifs function does not work. Attached a screenshot. 

5 Replies
With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. You could get a precise solution much faster with a file (w/out sensitive data). This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

Thank you for your understanding and patience

NikolinoDE
I know I don't know anything (Socrates)

@NikolinoDE I attached it here. For reference, the formulas in the participant tracking sheet tables pull from labels in cells A543-566. I'd like the demographics diagram sheet to pull from the participant tracking sheet. Again, the top level (age) pulls successfully, but not when I add age AND another demographic and so on and so forth. 

@maryk1770 

You can use the IFERROR function

See the examble in the file.

 

Thank you for your understanding

 

NikolinoDE

I know I don't know anything (Socrates)

 

 

Thanks for this! Unfortunately, it did not work :(. The formula isn't populating for the second level (MDD vs. CTL) or further. The diagram is to show how many people we have at each level so for every person who is 35-44 AND their group is CTL then they would be counted in that cell.

@maryk1770 

For COUNTIFS() you shall use range of the same size. Currently

=COUNTIFS(
  'Participant Tracking'!E31:E281, 'Participant Tracking'!A543,
  'Participant Tracking'!D31:D531,'Participant Tracking'!A555
)

If you change second range on 'Participant Tracking'!D31:D281 it shall return some value, not an error.