I want to calculate the total number of 'x's in a cell across a workbook (15 sheets) the calculation I have used is =COUNTA('sheet1:sheet15'!G3,"x") the problem I have is where if there is an x in G3 then the summary sheet adds them together which is fine but then adds another 1, e.g. if there is an 'x' in G3 on 10 of the sheets then excel totals this as 11. Can anyone help!!

Thanks

COUNTA is not for counting occurrences of a given value. Per the Excel help files:

COUNTA Counts thenumber of cells that are not emptyand the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.To count the number of occurrences of a given value in a range you would use COUNTIF.

=COUNTIF(A1:A10,"x")

Unfortunately, COUNTIF does not work across multiple sheets.

One method that should work for you is to use a COUNTIF in a cell in each sheet and then SUM those cells in your summary sheet.

My method (there are others):

I'll assume that the Summary sheet is the last sheet (Sheet16)

Group the sheets by Clicking the tab for the left most sheet (Sheet1) and then Shift-Click the tab for Sheet15.

In Sheet1, in a cell you know to be empty in every sheet (e.g. A1) enter:

=COUNTIF(G3,"x")

or

=IF(G3="x",1,0)

or any formula that will return a 1 if G3 contains an "x".

This will enter that same formula in A1 on every sheet.

In your summary sheet enter =SUM('Sheet1:Sheet15'!A1)

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Many thanks for your reply, I ended up going the long winded way with:

=SUMPRODUCT(--('Joe Bloggs'!G3="X"))+SUMPRODUCT(--('Fred Smith'!G3="X")) etc etc. which gave me the result I was looking for, there is however a further question you may be able to help me with, i.e. if Joe bloggs & John Smith can do a particular task (as the 'x' in G3 indicates) but Fred Smith cannot, is there a way that when I click on G3 on the summary sheet it will highlight only those names who can in list format.Many Thanks once again for your help.

I'm not sure why you used SUMPRODUCT when a SUM of COUNTIF's would have been a lot shorter. =SUM(COUNTIF('Joe Bloggs'!G3,"x"),COUNTIF('Fred Smith'!G3,"x"),COUNTIF('Sue Helms'!G3,"x"),etc.)

Since your 2nd question is not really related to your first, please post it in it's own thread with a relevant subject line.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll