Need to count over several worksheets

[Vanessa]

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

 

[erik.van.geit]

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

RANGE FORMULA (1st cell)
B2:D6 =COUNTIFmultisheets($A2,B$1)

[Table-It] version 06 by Erik Van Geit

in a module paste this 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]

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]

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]

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