Calculation adds extra number in total

May 10, 2011 at 01:27:54
Specs: Windows 7
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!!

See More: Calculation adds extra number in total

Report •

May 10, 2011 at 10:13:05
COUNTA is not for counting occurrences of a given value.

Per the Excel help files:

COUNTA Counts the number of cells that are not empty and 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.


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:




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.

Report •

May 10, 2011 at 10:37:38
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.

Report •

May 10, 2011 at 16:36:51
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.

Report •

Related Solutions

Ask Question