Excel - Counting Occurences in Same Cell, Different Sheets

[Dawber]

I currently have 5 sheets in a workbook. On the 6th sheet, in a specific cell (eg. "A1"), I would like a formula to count the number of times the letter "H" appears in the same cell (eg. "A1") on the other 5 sheets?

 

[SkipVought]

Hi,

Check out the COUNTIF function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dawber]

Thanks for responding Skip.

I'm familiar with the COUNTIF function but don't know the correct syntax for referencing several sheets within the formula?

eg.

COUNTIF(Sheet1!A1;Sheet2!A1,"H")

 

[TonyJollans]

The syntax for the 3D range is [blue][tt]Sheet1:Sheet2!A1[/tt][/blue] - but it doesn't work with COUNTIF

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

As Tony stated, COUNTIF does not seem to work across sheets.

Why do you have 5 sheets with similar data? Please describe your application.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

You can do what you want pretty easily with a user-defined function and a worksheet formula like:
=CountIf3D("Sheet1","Sheet5",A1,"H",COUNTA(Sheet1:Sheet5!A1))

The above formula uses COUNTA with a 3D range as a dummy variable. The COUNTA input doesn't figure into the answer, but rather makes the user-defined function recalculate only when the data in its underlying cells are changed.

Function CountIf3D(sFirstSheet As String, sLastSheet As String, rg As Range, criteria As String, CellCount As Long)
Dim i As Long, nFirst As Long, nLast As Long
Dim wb As Workbook
Set wb = rg.Worksheet.Parent
nFirst = wb.Worksheets(sFirstSheet).Index
nLast = wb.Worksheets(sLastSheet).Index
For i = nFirst To nLast
    CountIf3D = CountIf3D + Application.CountIf(wb.Worksheets(i).Range(rg.Address), criteria)
Next
End Function

To install a function in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

Brad

 

[SkipVought]

Dawber,

Excel is not designed to perform as you are describing. I surmize that your workbook design is thwarting the plethora of feature that Excel has built in. Consequently, you must do hand stands and jump thru hoops (special macros) to do something that SHOULD take only a simple COUNTIF function. Hence, my last question posted at 2 Jan 09 15:55 .

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GSCaupling]

Dawber,

With only five sheets involved, you could simply stack functions:

=COUNTIF(Sheet1!A1,"*h*")+COUNTIF(Sheet2!A1,"*h*")...

Note: [red]This is accurate only if you have one occurance (or none) of "H" per cell.[/red] In other words, cell contents of HigH, Horse, and casH all return 1.

As Skip suggested, it would probably be helpful if you provided some more details about your actual data.

Good luck!
GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[Dawber]

Tony, byundt and GSCaupling, thanks for the responses, you have each given me something that I didn't know or hadn't thought of.

Skip, the application is to check holiday (vacation) cover for staff. Each individual has the same sheet, with days numbered horizontally and months vertically. They are then asked to complete holiday requests by placing an "H" in the relevant cell. Staff are grouped in 5's for the purposes of cover and cannot have more than 2/5 on holiday at any time.

The purpose of the formula that I am looking for is to highlight any areas where this 2/5 may have been exceeded, without having to manually check and compare individual sheets.

 

[TonyJollans]

If the cells only contain either "H" or nothing, you could use COUNTA, which does work with a 3-D Range.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Dawber]

Thanks Tony, much appreciated.

 

[SkipVought]

You would be a WHOLE lot better off, having ALL your data in one sheet. It will GREATLY simplify the maintenance, analysis and reporting capabilities of your application, without a doubt!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

Since you cannot use a reference to another worksheet in Conditional Formatting, I changed the user-defined function to make it volatile and eliminate the dummy variable. You can therefore use a "Formula Is" criteria like this in each of the cells on the five worksheets:
=Countif3D("Sheet1","Sheet5",A1,"H")>2

If cell A1 is at the top left corner of the range you are conditional formatting, then each cell in the range will be comparing itself to like cells in the other worksheets.

Function CountIf3D(sFirstSheet As String, sLastSheet As String, rg As Range, criteria As String)
Dim i As Long, nFirst As Long, nLast As Long
Dim wb As Workbook
Application.Volatile
Set wb = rg.Worksheet.Parent
nFirst = wb.Worksheets(sFirstSheet).Index
nLast = wb.Worksheets(sLastSheet).Index
For i = nFirst To nLast
    CountIf3D = CountIf3D + Application.CountIf(wb.Worksheets(i).Range(rg.Address), criteria)
Next
End Function

Brad

 

[SkipVought]

Since you cannot use a reference to another worksheet in Conditional Formatting...

More complications that your design has caused you, that would not be an issue, had all you data been in one table.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dawber]

I understand what you are saying Skip and will take it on board, however I have not designed this particular sheet, which is used by 15,000 users. I am merely looking for an easier method of reviewing 40 or so of them. Thanks once again for your assistance and the other posters, who have taken time to make my life easier.

 

[byundt]

If you don't want to use VBA code, then you can create named ranges that point to the corresponding cell on the various worksheets. You need one such named range for each worksheet. These named ranges may then be used in Conditional Formatting to highlight cells whenever there are three or more people in a group taking Holiday on the same date.

1) Select cell A1 in Sheet1, then create a named range called Sheet1Cell using a "Refers to" like:
=INDIRECT(CELL("address",Sheet1!A1))
2) Repeat step 1 for each of the other worksheets
3) Conditionally Format the cells on Sheet1 using a "Formula is" criteria like:
=((Sheet1Cell="H")+(Sheet2Cell="H")+(Sheet3Cell="H")+(Sheet4Cell="H")+(Sheet5Cell="H"))>2
4) Repeat step 3 for each of the other worksheets

The named ranges will adapt to changes in worksheet name and cell location automatically. No matter where you use it, the formula =Sheet1Cell always refers to the value in the same cell on Sheet1 as the location of the formula. If you put the formula in Sheet6 cell D10, you get the value from Sheet1 cell D10.

The Conditional Formatting will color the corresponding cells on each worksheet whenever there are three or more "H" values on those sheets.

Brad

 

[kjv1611]

which is used by 15,000 users.

Even though you may find a current work around for this one instance, if this workbook is used by or in any way affects 15,000 employees, it'd be in your employer's best interest to rethink things.

With that many employees, assuming each one has multiple entries as well, you may be better off having someone create a database, or add this into a database, and then spit out reports ONLY in Excel format.

The database could be setup to allow for changes via a web application.

This would be a bigger long-term fix, but honestly, with that many people, keeping it in multiple worksheets in Excel is just ASKING for a big headache at some point down the road.

--

"If to err is human, then I must be some kind of human!" -Me