Is it possible to do a countif using the same cell reference for every
worksheet?

Like a Shorthand of doing
=COUNTIF(Sheet2!A1,1)+COUNTIF(Sheet3!A1,1)...+COUN TIF(Sheet15!A1,1)

Thanks in advance
Kieran


--
kmh987
------------------------------------------------------------------------
kmh987's Profile: http://www.excelforum.com/member.php...o&userid=33533
View this thread: http://www.excelforum.com/showthread...hreadid=533240

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A1"), 1))


where MySheets is the named of a defined range like H2:H10 where you out ALL
the sheet names,

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"kmh987" wrote in
message ...

Is it possible to do a countif using the same cell reference for every
worksheet?

Like a Shorthand of doing
=COUNTIF(Sheet2!A1,1)+COUNTIF(Sheet3!A1,1)...+COUN TIF(Sheet15!A1,1)

Thanks in advance
Kieran


--
kmh987
------------------------------------------------------------------------
kmh987's Profile:
http://www.excelforum.com/member.php...o&userid=33533
View this thread: http://www.excelforum.com/showthread...hreadid=533240

Hi Kieran,
I think you have to construct a list of all those A1 values on the one
sheet then use the countif formula on that list.
The following formula filled down 14 rows will construct that list...

=INDIRECT("Sheet" & ROW(A2) & "!A1")

Ken Johnson

You'll definitely need Peo's solution if you have changed your Sheet's
names!
That SUMPRODUCT formula never ceases to amaze me.
Ken Johnson

Thanks for all your help so far i was very suprised at how quick and the
number of responses (as this is my first post) but i am having a bit of
trouble converting the that formula into my example
here is my actual formula at the moment

=COUNTIF('AU05-02'!D16,"X")+COUNTIF('AU05-09'!D16,"X")+COUNTIF('AU05-12'!D16,"X")+COUNTIF('AU05-14'!D16,"X")+COUNTIF('AU05-23'!D16,"X")+COUNTIF('AU04-24'!D16,"X")+COUNTIF('AU05-29'!D16,"X")+COUNTIF('AU05-34'!D16,"X")+COUNTIF('AU05-36&7'!D16,"X")+COUNTIF('AU05-46'!D16,"X")+COUNTIF('AU05-50'!D16,"X")+COUNTIF('AU05-58'!D16,"X")+COUNTIF('AU05-60'!D16,"X")+COUNTIF('AU05-61'!D16,"X")+COUNTIF('AU05-68'!D16,"X")

Here is a picture that might help you to understand what i would like
to be done.
4642
this survey is on every one of the 16 worksheets but the first
worksheet (Summary 2004_5) is blank and i would like a summary of all
the "X" on all the other worksheets.

Any more help would be greatly appreciated
Thanks
Kieran


+-------------------------------------------------------------------+
|Filename: excelexample.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4642 |
+-------------------------------------------------------------------+

--
kmh987
------------------------------------------------------------------------
kmh987's Profile: http://www.excelforum.com/member.php...o&userid=33533
View this thread: http://www.excelforum.com/showthread...hreadid=533240

Hi Kieran,
Does your formula work?
If it does work, I can't see the point in not using it. Sure it takes a
while setting up in the first cell, but once that's done it's just a
matter of copying and pasteing into all the other cells. The addresses
are all relative so they change to suit the new cells.


I just spent some time setting up a workbook with 16 sheets with what I
thought were your sheet names then tested your formula only to discover
it returns #VALUE.
When I had I closer look at your sheet names I discovered I had
incorrectly assumed the pattern was AU05-02, AU05-03, AU05-04...etc so
I thought the formula only failed because of that mistake I made.
After fixing up the sheet names I re-tested your formula, only to get
the same result, #VALUE.

So, I guess you have had the same problem.

I have used a different formula that doesn't use COUNTIF. Instead it
use SUM(IF(Sheet2!D16="X",1,0),IF(Sheet3!D16="X",1,0), etc and,
it works!

Copy this into D16 of your Summary Sheet then just copy it into all the
other cells corresponding with answer cells on the response sheets, and
hopefully you will get the X counts for each possible response to each
question.

=SUM(IF('AU05-02'!D16="X",1,0),IF('AU05-09'!D16="X",1,0),IF('AU05-12'!D16="X",1,0),IF('AU05-14'!D16="X",1,0),IF('AU05-23'!D16="X",1,0),IF('AU05-24'!D16="X",1,0),IF('AU05-29'!D16="X",1,0),IF('AU05-34'!D16="X",1,0),IF('AU05-36&7'!D16="X",1,0),IF('AU05-46'!D16="X",1,0),IF('AU05-50'!D16="X",1,0),IF('AU05-58'!D16="X",1,0),IF('AU05-60'!D16="X",1,0),IF('AU05-61'!D16="X",1,0),IF('AU05-68'!D16="X",1,0))

Good Luck

Ken Johnson

great
thanks for your help all of you it works great now
Kieran


--
kmh987
------------------------------------------------------------------------
kmh987's Profile: http://www.excelforum.com/member.php...o&userid=33533
View this thread: http://www.excelforum.com/showthread...hreadid=533240