COUNTIF across multiple worksheets

[slbutter1]

Would someone know what I am doing wrong here? I need to count the frequency/occurance of letters "A", "B", "C" etc. that occur in the exact same cell in multiple worksheets of a single workbook. COUNTIF works ok on a single sheet query for me but gives the "VALUE" error message when I insert the sheet1:sheetxxx range.
Thank you,
Stephen Butter

 

[Biz]

Hi Aladin,

Is there countif across multiple sheets for Excel 2003?
I'm trying to count text occurrences.


Biz

Hi Aladin,

I had similar problem in Excel 2003 and used formula for COUNTIF across multiple worksheets

=SUMPRODUCT(COUNTIF(INDIRECT("'"&mSheets&"'!K2:K1000"),K15))

Thanks again for Sumproduct and Sumif trick.

Biz

 

[Aladin Akyurek]

Hi Aladin,

I had similar problem in Excel 2003 and used formula for COUNTIF across multiple worksheets

=SUMPRODUCT(COUNTIF(INDIRECT("'"&mSheets&"'!K2:K1000"),K15))

Thanks again for Sumproduct and Sumif trick.

Biz

That's is indeed the appropriate formula expression.

 

[Biz]

That's is indeed the appropriate formula expression.

Hi Aladin,

If I wanted to do countif based on multiple criteria. I have formula that works.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&mSheets&"'!K2:K1000"),{"Admin","Finance"}))

Is it possible to avoid hard coding multiple countif criteria?

Kind Regards,

Biz

 

[Aladin Akyurek]

Hi Aladin,

If I wanted to do countif based on multiple criteria. I have formula that works.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&mSheets&"'!K2:K1000"),{"Admin","Finance"}))

Is it possible to avoid hard coding multiple countif criteria?

Kind Regards,

Biz

As you see from the formula you have, Admin and Finance are in an horizontal array (vector). If you want to substitute a range housing these two criteria, you'll need to opt for a horizontal range: For example...

A2:B2

but not

A2:A3

Thus:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&mSheets&"'!K2:K1000"),A2:B2))

If you insist on having A2:A3, you'll need to invoke a more expensive formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&mSheets&"'!K2:K1000"),TRANSPOSE(A2:A3)))

which must be confirmed moreover with control+shift+enter, not just enter.

 

[Biz]

As you see from the formula you have, Admin and Finance are in an horizontal array (vector). If you want to substitute a range housing these two criteria, you'll need to opt for a horizontal range: For example...

A2:B2

but not

A2:A3

Thus:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&mSheets&"'!K2:K1000"),A2:B2))

If you insist on having A2:A3, you'll need to invoke a more expensive formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&mSheets&"'!K2:K1000"),TRANSPOSE(A2:A3)))

which must be confirmed moreover with control+shift+enter, not just enter.

Hi Aladin,

Thank you very much for your help.

Awesome mate.

Biz

 

[Aladin Akyurek]

Hi Aladin,

Thank you very much for your help.

Awesome mate.

Biz

You are welcome. Thanks for providing feedback.

 

[Ames]

Hi,

I'm not sure if any of the formulas that have been posted thus far in this thread is working for me. I'm trying to count text occurrences given certain conditions (e.g. in four columns of data, I would like to count the occurrences of A & B, A & C, and A & D).

This is the formula I am currently using:

=SUM(((A:A=A3)*(B:B=C1)),((A:A=A3)*(K:K=C1)),((A:A=A3)*(L:L=C1)))

This formula will count multiple criteria, but I'm not exactly sure how to go about making it count them accross different worksheets. I'm not too familiar with Excel and I believe the INDIRECT() function will help, but again, I'm not exactly sure how to utilize it with my current function. I'm running Excel 2003.

Anyone's help is greatly appreciated. =)

 

[all4excel]

Dear Alladin,

I am finding this POST very useful however, I just needed to know how do we replace the COUNTIFS scenario if we are working across Multiple Sheets?

I mean in countifs I get the answer but I am afraid not everyone in my compnay is using 2007 and above and therefore it becomes a little..

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&Shts&"'!$A$30:$A$40"),A3,INDIRECT("'"&Shts&"'!$F$30:$F$40"),""))

I need to count the no of blanks in the Col F for Multiple Sheets which are housed in the Defined Name "Shts"

Can anyone help me out , I am not trying to hijack the thread its just a matter of an extension..

If you want I can start a New Thread if required..

all4excel

 

[burz]

Small bump.

This is basically the formula I created although it is only counting the first instance it finds.

=COUNTIF(INDIRECT("'"&list&"'!B8"),"YES")

I have three correct sheet names inside the named range "list", and in B8 on all of those sheets is the word "YES" although it only counts the value based on the first sheet in the list and ignores everything else. There are no empty spaces or invisible characters after the word yes or in any of the sheet names.

 

[Aladin Akyurek]

Small bump.

This is basically the formula I created although it is only counting the first instance it finds.

=COUNTIF(INDIRECT("'"&list&"'!B8"),"YES")

I have three correct sheet names inside the named range "list", and in B8 on all of those sheets is the word "YES" although it only counts the value based on the first sheet in the list and ignores everything else. There are no empty spaces or invisible characters after the word yes or in any of the sheet names.

You need to sum the multiple counts which obtain...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&list&"'!B8"),"YES"))