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

 

[Aladin Akyurek]

Hi Aladin,
Thanks for your answer!
However, you will probably think I'm a retard but I dont understand what you want me to do:

http://img94.imageshack.us/i/excelf.jpg/
thats what you want me to do ?
then I I go in the formula bar to put SheetList it changes the Cell name in A2, that is Sheet1

I'm not sure that's what you meant

Insert a new worksheet.
Rename this worksheet Admin.
Activate Admin.
Enter in cell A2: Sheet1
Enter in cell A3: Sheet2
Select A2:A3.
Click in the Name Box on the Fx (Formula) Bar.
Type SheetList in the Name Box and hit the enter key.

 

[Jeffy14]

ok Aladin thx for being so patient with me =) again i'm really sorry to suck so much
SO i think I got it this time!
http://img143.imageshack.us/i/durn.jpg/

I selected A2:A3 and named them SheetList in the NameBox =)
But now i'm trying your formula and it gives me an error. I dont know where it's coming from. It seems it's in the syntax. Do you have any ideas ?
http://img709.imageshack.us/i/dur22.jpg/

Thankyuo very much !! that's really nice of you

 

[Aladin Akyurek]

ok Aladin thx for being so patient with me =) again i'm really sorry to suck so much
SO i think I got it this time!
http://img143.imageshack.us/i/durn.jpg/

I selected A2:A3 and named them SheetList in the NameBox =)
But now i'm trying your formula and it gives me an error. I dont know where it's coming from. It seems it's in the syntax. Do you have any ideas ?
http://img709.imageshack.us/i/dur22.jpg/

Thankyuo very much !! that's really nice of you

What did you exactly try out?

 

[Jeffy14]

When I try your formula
=SUMPRODUCT(COUNTIFS(
INDIRECT("'"&SheetList&"'!A:A"),"A",
INDIRECT("'"&SheetList&"'!H:H"),"B",
INDIRECT("'"&SheetList&"'!AC:AC"),"C"))

it gives me an error, saying the formula contains an error.
Does it work for you ?
I tried to input thiis formula on the admin sheet Cell E8 as shown on the screenshot
do you know why ?

 

[Aladin Akyurek]

When I try your formula
=SUMPRODUCT(COUNTIFS(
INDIRECT("'"&SheetList&"'!A:A"),"A",
INDIRECT("'"&SheetList&"'!H:H"),"B",
INDIRECT("'"&SheetList&"'!AC:AC"),"C"))

it gives me an error, saying the formula contains an error.
Does it work for you ?...

Maybe some undesired, invisible char... Here it's again:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!A:A"),"A",INDIRECT("'"&SheetList&"'!H:H"),"B",INDIRECT("'"&SheetList&"'!AC:AC"),"C"))

 

[Jeffy14]

Omg I think it's working !!!
you saved my life
actullay the problem was due to the semi Colon
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!A:A");"A";INDIRECT("'"&SheetList&"'!B:B");"B";INDIRECT("'"&SheetList&"'!C:C");"C"))

I think it's good now. Thx a lot i'll let you know!!

 

[hardeep.kanwar]

Create a range housing the relevant sheet names, name this range SheetList, and invoke:

=SUMPRODUCT(COUNTIFS(
   INDIRECT("'"&SheetList&"'!A:A"),"A",
   INDIRECT("'"&SheetList&"'!H:H"),"B",
   INDIRECT("'"&SheetList&"'!AC:AC"),"C"))

Hello Sir,

I am Facing Same Problem, but I want both Count and Sum

See the Link

http://www.mrexcel.com/forum/showthread.php?p=2343871#post2343871


Pls Me

 

[Aladin Akyurek]

Hello Sir,

I am Facing Same Problem, but I want both Count and Sum

See the Link

http://www.mrexcel.com/forum/showthread.php?p=2343871#post2343871


Pls Me

The set up is the same. If you have one condition for counting, use COUNTIF. And if you have one condition for summing, use SumIf. For multi-conditional count and sum, you can use COUNTIFS and SUMIFS respectively if you are on Excel 2007 or beyond.

 

[hardeep.kanwar]

The set up is the same. If you have one condition for counting, use COUNTIF. And if you have one condition for summing, use SumIf. For multi-conditional count and sum, you can use COUNTIFS and SUMIFS respectively if you are on Excel 2007 or beyond.

Man You are My Hero

You save my Life

Lots of Lots of Thanksray:ray:ray:ray:

 

[Biz]

The set up is the same. If you have one condition for counting, use COUNTIF. And if you have one condition for summing, use SumIf. For multi-conditional count and sum, you can use COUNTIFS and SUMIFS respectively if you are on Excel 2007 or beyond.

Hi Aladin,

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


Biz