Countif Formular cannot multiple sheets ?

[domingsun134]

Anything Wrong in my formula ? it cannot work .

=COUNTIF('Yan Zhao 8:31'!C5:C58,"NAM LEE")

please help me correct it .

[FDibbins]

Hi and welcome to the forum

1st, please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the adress bar)

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

2nd It would be almost impossible to offer a suggestion as to why that doesnt work, without seeing a sample of the data you are using it on. Thats like saying...my car wont start, why?

[Tony Valko]

Yan Zhao 8:31 is an invalid sheet name.

Sheet names can not contain the colon character.

Do you mean:

Sheet Yan Zhao 8 thru sheet Yan Zhao 31?

If so, change the thread title as requested then we'll be able to give you a solution.

[FDibbins]

Tony, congrats on the Expert status, very well deserved and about time

[domingsun134]

Tony ,right .

I want select the range in Sheet Yan Zhao 8 to Sheet 31

[Tony Valko]

Hmmm...

I've been an "expert" for quite a while, now.

That's as far as I'm going.

[Tony Valko]

Try it like this...

A2 = NAM LEE

=SUMPRODUCT(COUNTIF(INDIRECT("'Yan Zhao "&ROW(INDIRECT("8:31"))&"'!C5:C58"),A2))

[domingsun134]

Tony , We do it easy abit .

lets say i have sheet 1 and sheet 2 .

I want count the supplier named Nam Lee .

the range is C5:C58.

[Tony Valko]

If the REAL sheet names are Sheet 1 and Sheet 2, then:

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet "&{1,2}&"'!C5:C58"),A2))

If the sheet names are random names and don't follow a sequential pattern then you'll have to list the sheet names in a range of cells and refer to that range.

With the sheet names in the range B2:B10...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B2:B10&"'!C5:C58"),A2))

[Pete_UK]

Another approach:

You can't use 3-D references with COUNTIF, but what you can do is to put the COUNTIF formula in the same cell on every sheet, and then use a SUM across the sheets. So, put this formula in the same cell on sheets Yan Zhao 8 to Yan Zhao 31:

=COUNTIF(C5:C58,"NAM LEE")

an easy way to do this is to group those sheets together, then enter the formula (say into cell X1), then ungroup the sheets. Then you can have this formula on your summary sheet:

=SUM('Yan Zhao 8:Yan Zhao 31'!X1)

Hope this helps.

Pete