Need to count over several worksheets

Vanessa

New Member
Joined
Mar 26, 2006
Messages
23
Hi,

I have some data that is for different stores. The data is in the same format/same cells but it is in 10 worksheets, each for a different store.

The cells in the 10 worksheets have either a Y or an N or they are blank. What I would like in a summary sheet is to count the total of N and Y and the blanks. So I would like the computer to check for e.g cell C5 in all the 10 sheets and come back with a total of for e.g 6 Y, 2 N and 2 blanks.

I do not know if its possible toget the answer in 1 sheet. However if that is not possible it would still be OK if I could get the answer in 3 sheets. One sheet totalling the no of Y, the other N and the third counting the no of blanks.

Thanks
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Vanessa,

you can apply formulas looking in several sheets - so called 3D-formulas like
=SUM('First SheetName:Last Sheetname'!A1)
unfortunately this is impossible using countif
an extra column on each sheet would solve the problem, but I think a User Defined Function will be better in this case

  A  B   C  D  
1    Yes No    
2 A1 1   3  6  
3 A2 2   5  3  
4 A3 0   0  10 
5 A4 0   0  10 
6 A5 0   0  10 

test

[Table-It] version 06 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
B2:D6 =COUNTIFmultisheets($A2,B$1)

[Table-It] version 06 by Erik Van Geit

in a module paste this code
Code:
Option Explicit

Function COUNTIFmultisheets(c As String, cntItem As String)
Dim i As Integer
Dim cnt As Integer

Application.Volatile
    For i = 2 To Sheets.Count
    If LCase(Sheets(i).Range(c)) = LCase(cntItem) Then cnt = cnt + 1
    Next i
    
COUNTIFmultisheets = cnt

End Function
I would recommend not using blank as is but replace it by something like N/A

kind regards,
Erik
 

Vanessa

New Member
Joined
Mar 26, 2006
Messages
23
Thanks for that, however i am not too familiar with VBA.

Is there anything in excel that I can use. Its Ok if I can get the totals in three seperate sheets ( i.e one for Y, one for N and the last one for n/a)
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Vanessa,

would you reject a simple solution because you're not "famililiar" ?
to my knowledge there is no solution unless a system which needs extra columns which could involve some problems ...

here step by step
IMPORTANT: forget about your project for a moment and try to reconstruct the example:
1. create a new workbook with several sheets (doesn't matter how much)
2. start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
3. In the module (the white area at the right), paste the code I gave you;
4. setup your workbook as follows
4a. first sheet like the table above
enter in B2 this formula =COUNTIFmultisheets($A2,B$1)
copy down and accross
4b. next sheets: type some YESses and NOs in cells A1 to A5
5. check your first sheet

once you get this running, you can adapt it to your workbook

best regards,
Erik
 

smokenack

Active Member
Joined
Dec 12, 2003
Messages
351
Why not just link a sheet to all ten and then use COUNTIF eg:

Cell A1 =Sheet2!B1 copied down
Cell B1 =Sheet3!B1 copied down etc

and then use COUNTIF to total the y,n,and blanks

Nick
 

Forum statistics

Threads
1,144,610
Messages
5,725,297
Members
422,607
Latest member
joce

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top