Countifs not counting, returns #value

**Qualo_Jinn** · 12-09-2011, 01:22 PM

I'm using Countifs thusly:

Please Login or Register  to view this content.

Everything works right up until it gets to the Countifs formula, then it returns the #value. Attached is the workbook. On the October page I've taken out the indirects but on the numbers sheet the formula (w/indirect) has gone awry. I've tried confirming with CSE (grasping at straws) but, of course, it didn't help. I have a feeling it's the indirect function but don't know how to correct it.

Example Maintenance Log.xls

**Haseeb Avarakkan** · 12-09-2011, 01:36 PM

All the ranges should be in equal size. The last range INDEX(INDIRECT("'"&B$41&"'!$P$4:$T$300")... it should be $P$4:$T$430.

Try this version,

Please Login or Register  to view this content.

Also, C3 to down & across, you can reduce the formula to,

=IFERROR(COUNTIF(INDIRECT("'"&TEXT(B3,"mmmm")&"'!G4:G430"),B3),"")

Then apply a custom cell format to hide the zero values as

0;;;

INDIRECT is volatile function. if you have many of them will make the calculation slow.

**Qualo_Jinn** · 12-09-2011, 03:41 PM

I appreciate your rapid response. Thank you. That worked well. Unfortunately, Match is resulting in 5 for everybody instead of the proper column number. Could I bother for your assistance one more time?