Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using the same cell on every sheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using the same cell on every sheet
=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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using the same cell on every sheet
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using the same cell on every sheet
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using the same cell on every sheet
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using the same cell on every sheet
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using the same cell on every sheet
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using the same cell on every sheet
Hi Kieran,
Great to hear you've got it working! Thanks for the feedback. Ken Johnson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using the same cell on every sheet
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 ViewFormula 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text from one sheet cell to appear in another sheet cell | Excel Worksheet Functions | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Cell linked to a range of cell values in different sheet | Excel Discussion (Misc queries) | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Automatic cell increment with data from sheet 1 to sheet 2 | Excel Worksheet Functions |