Countif using the same cell on every sheet

[kmh987]

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)...+COUNTIF(Sheet15!A1,1)

Thanks in advance
Kieran

 

[Peo Sjoblom]

=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

 

[Ken Johnson]

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

 

[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

 

[kmh987]

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 |
+-------------------------------------------------------------------+

 

[Ken Johnson]

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

 

[kmh987]

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

 

[Ken Johnson]

Hi Kieran,

Great to hear you've got it working!
Thanks for the feedback.

Ken Johnson

 

[Ken Johnson]

Hi Kieran,
I've been doing a bit of Help File reading and have discovered that
setting up that Summary sheet's actually much much easier!
The Help file I read was about 3D references in formulas.
Only a small number of functions can be used when referencing cells on
different sheets (COUNTIF is not one of them).
COUNT (counts numbers) and COUNTA (counts numbers and text) can be
used.
With your survey sheets, when a respondent answers each question they
type an "X" in one cell and leave the other cells blank, so there
really wasn't the need for the SUM, IF "X" combination formula, COUNTA
is the only function needed, since the cells either have a character
(COUNTA = 1) or are blank (COUNTA = 0).

Setting up the Summary sheet is as simple as..

1. First make sure that the Summary sheet's tab is the first tab on the
left (or the last tab on the right), i.e., not in-between any of the
respondent's sheet tabs.
2. Make sure that the Formula bar is visible (Go View>Formula Bar if it
isn't)
3. Select D16 on the Summary sheet and type in "=COUNTA(" (w/o the
speech marks, and DON'T press Enter yet)
4. Click on the leftmost Respondent sheet tab, then press and hold
SHIFT while you click on the rightmost Respondent sheet tab. Depending
on the sheet tab order, in the Formula bar you should then see
something like...

=COUNTA('AU05-02:AU05-68'!

5. Now click at the end of the formula in the Formula Bar and finish it
off by adding "D16)" so that the final formula looks like...

=COUNTA('AU05-02:AU05-68'!D16)

6. Now press Enter. The Summary sheet should come into view and the
count of X's should be in D16.
7. Now just copy that much much smaller formula into all the other
relevant cells on the summary sheet.

I guess there is no point in re-doing the Summary sheet since it is
working, but at least you will be able to do that sort of stuff (3D
formulas) much faster next time.

Search "Refer to the same cell or range on multiple sheets" in Help
for the list of Formulas that can be used.


The old formula's good for a laugh at least!

Ken Johnson