Apr 12 2021 11:55 AM
Hi,
I have created a spreadsheet with the formula below to count how many times a name appears in a dropdown list across 89 sheets in excel.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!B9"),C4))
It is missing one though and I can't find out why.
I have double checked each sheet and all the names are correct (there are 89 names, but only 88 are being counted)
Can anyone suggest why it's not counting one of the sheets please?
Thank you
Suzy
Apr 12 2021 12:01 PM
Perhaps one of the names has a space after it (or before it), e.g. "Calum " instead of "Calum".
Apr 12 2021 12:03 PM
Apr 12 2021 12:32 PM
You could start a manual check using the array
= COUNTIFS(INDIRECT("'"&SheetNames&"'!B9"),"Calum")
Apr 12 2021 01:07 PM
Or a sheet could be missing from the SheetNames list.