| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 but i would also like to covert it to VB code =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. 4643 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. If at all possible, if a new worksheet is added or name is changed it would automatically include that one aswell Thanks in advance Kieran +-------------------------------------------------------------------+ |Filename: excelexample.jpg | |Download: http://www.excelforum.com/attachment.php?postid=4643 | +-------------------------------------------------------------------+ -- kmh987 ------------------------------------------------------------------------ kmh987's Profile: http://www.excelforum.com/member.php...o&userid=33533 View this thread: http://www.excelforum.com/showthread...hreadid=533335 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Countif using the same cell on every sheet
Function CountACrossSheets(cell As String, testValue) As Long
Dim sh As Worksheet Dim rng As Range Dim tmp As Long For Each sh In ThisWorkbook.Worksheets If sh.Name < Range("A1").Parent.Name Then Set rng = sh.Range(cell) tmp = tmp + Application.CountIf(rng, testValue) End If Next sh CountACrossSheets = tmp End Function use like this =CountAcrossSheets("D16","X") it will count all sheets except the sheet the function is used in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kmh987" wrote in message ... Is it possible to do a countif using the same cell reference for every worksheet? Like a shorthand of doing but i would also like to covert it to VB code =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('AU 05-36&7'!D16,"X")+COUNTIF('AU05-46'!D16,"X")+COUNTIF('AU05-50'!D16,"X")+COUN TIF('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. 4643 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. If at all possible, if a new worksheet is added or name is changed it would automatically include that one aswell Thanks in advance Kieran +-------------------------------------------------------------------+ |Filename: excelexample.jpg | |Download: http://www.excelforum.com/attachment.php?postid=4643 | +-------------------------------------------------------------------+ -- kmh987 ------------------------------------------------------------------------ kmh987's Profile: http://www.excelforum.com/member.php...o&userid=33533 View this thread: http://www.excelforum.com/showthread...hreadid=533335 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Countif using the same cell on every sheet
slight adjustment
Function CountACrossSheets(cell As String, testValue) As Long Dim sh As Worksheet Dim rng As Range Dim tmp As Long For Each sh In ThisWorkbook.Worksheets If sh.Name < Application.Caller.Parent.Name Then Set rng = sh.Range(cell) tmp = tmp + Application.CountIf(rng, testValue) End If Next sh CountACrossSheets = tmp End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kmh987" wrote in message ... Is it possible to do a countif using the same cell reference for every worksheet? Like a shorthand of doing but i would also like to covert it to VB code =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('AU 05-36&7'!D16,"X")+COUNTIF('AU05-46'!D16,"X")+COUNTIF('AU05-50'!D16,"X")+COUN TIF('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. 4643 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. If at all possible, if a new worksheet is added or name is changed it would automatically include that one aswell Thanks in advance Kieran +-------------------------------------------------------------------+ |Filename: excelexample.jpg | |Download: http://www.excelforum.com/attachment.php?postid=4643 | +-------------------------------------------------------------------+ -- kmh987 ------------------------------------------------------------------------ kmh987's Profile: http://www.excelforum.com/member.php...o&userid=33533 View this thread: http://www.excelforum.com/showthread...hreadid=533335 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Countif using the same cell on every sheet
great thanks for your help i really appreciate it Kieran -- kmh987 ------------------------------------------------------------------------ kmh987's Profile: http://www.excelforum.com/member.php...o&userid=33533 View this thread: http://www.excelforum.com/showthread...hreadid=533335 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Countif using the same cell on every sheet
Help i have entered the VB code and then i go to enter the formula into the cell but i get a #NAME? error and i can't work out why?? -- kmh987 ------------------------------------------------------------------------ kmh987's Profile: http://www.excelforum.com/member.php...o&userid=33533 View this thread: http://www.excelforum.com/showthread...hreadid=533335 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Countif using the same cell on every sheet
You need to put the VBA code in a standard code module, not in a worksheet
code module, nor ThisWorkbook. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kmh987" wrote in message ... Help i have entered the VB code and then i go to enter the formula into the cell but i get a #NAME? error and i can't work out why?? -- kmh987 ------------------------------------------------------------------------ kmh987's Profile: http://www.excelforum.com/member.php...o&userid=33533 View this thread: http://www.excelforum.com/showthread...hreadid=533335 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Countif using the same cell on every sheet
you are an absolute legend thanks alo -- kmh98 ----------------------------------------------------------------------- kmh987's Profile: http://www.excelforum.com/member.php...fo&userid=3353 View this thread: http://www.excelforum.com/showthread.php?threadid=53333 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif and Sumif with refrence value a Cell on another Sheet in theWorkbook | Excel Discussion (Misc queries) | |||
| using countif on sheet 2 | Excel Discussion (Misc queries) | |||
| Countif using the same cell on every sheet | Excel Worksheet Functions | |||
| countif more than one sheet/tab and sumproduct | Excel Worksheet Functions | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Hybrid Mode
