COUNTIF with INDIRECT Function

**somesoldiers** · 03-23-2011, 01:12 PM

hi Guys

Can't seem to get this right?

=COUNTA('Jan 2011'!L:L)-1, want to use indirect function to ref cell B8 which has Jan 2011 so formula will be dynamic enough to use c8 Feb 2011, d8 Mar 2011 etc

=COUNTA((INDIRECT("'"&B8&"'!")*L:L)-1)- just gives 1

thanks

Noel

**DonkeyOte** · 03-23-2011, 01:17 PM

You need to include Column L in your string - INDIRECT essentially converts a string to a range

Please Login or Register  to view this content.

**somesoldiers** · 03-23-2011, 01:29 PM

Thanks DonleyOte- that works, is the countif fuction different- can't seem to apply below?

=COUNTIF(INDIRECT(“’”&B$8&”'!L:L”,"CONFIRMED"))

**DonkeyOte** · 03-23-2011, 01:41 PM

Your closing parentheses are misplaced - you should have one to close INDIRECT (prior to specifying COUNTIF criteria) and one to close the COUNTIF.

**somesoldiers** · 03-24-2011, 05:12 AM

got it, thanks

=COUNTIF(INDIRECT("'"&B8&"'!L:L"),"CONFIRMED")

**somesoldiers** · 03-24-2011, 06:55 AM

Hi Guys

back again, I would like to count where both BB = A17 and LL = CONFIRMED, below is adding amount of times each occurs. probably need an array but could never get to grip with these

Thanks lot

Noel

=COUNTIF(INDIRECT("'"&B$9&"'!B:B"),$A17)+COUNTIF(INDIRECT("'"&B9&"'!L:L"),"CONFIRMED")

**DonkeyOte** · 03-24-2011, 07:10 AM

XL2007 and above use COUNTIFS

Prior to XL2007 you're left with SUMPRODUCT - given you're using this in conjunction with INDIRECT (Volatile) be sure to use in moderation - also keep precedent ranges optimised (small as possible)

Please Login or Register  to view this content.

**somesoldiers** · 03-24-2011, 07:13 AM

Got it, thanks again

**jobindaniel** · 01-20-2012, 10:38 AM

Hello Somesoldiers and DonkeyOte.. Please help..

For one comment by somesoldiers, there was a problem with parantheses and then it was posted that this formula works
=COUNTIF(INDIRECT("'"&B8&"'!L:L"),"CONFIRMED")

But I have been struggling for the past few hours to fix my formula =COUNTIF(INDIRECT(“‘”&A1&“‘!$B$1:$B$100),"test") . It doesn't work. I am using Excel 2010

I also copied the formula that SOMESOLDIERS managed to use successfully. =COUNTIF(INDIRECT("'"&B8&"'!L:L"),"CONFIRMED") It doesn't work either. Any quick help will be highly appreciated.

Also, please note that its not a #REF error , it is the pop up complaining "The formula you typed contains an error"

**jobindaniel** · 01-20-2012, 12:22 PM

I solved the problem... It was so silly..
I copied the formula from a web page initially and the double quotes (") were wrongly interpreted by excel. I am not sure if anyone has heard this before. But thesame formula worked when I typed it myself. Thanks anyways .