Formula Help Please.

Copper Contributor

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? 

 

1.jpg

 

Thank you 

Suzy

 

4 Replies

@SuzyBarnett 

Perhaps one of the names has a space after it (or before it), e.g. "Calum " instead of "Calum".

I don't think so, as the name comes from a drop down list, so they should all be the same.
I'll check though, Thank you.

@SuzyBarnett 

You could start a manual check using the array

= COUNTIFS(INDIRECT("'"&SheetNames&"'!B9"),"Calum")

@SuzyBarnett 

Or a sheet could be missing from the SheetNames list.