# Thread: Counting total numbers (Excel 2003)

1. ## Counting total numbers (Excel 2003)

Hi!
I have some problems writing a formula where I could count how many numbers of a kind i have on one particular column through out 10 different worksheets.

2. ## Re: Counting total numbers (Excel 2003)

Could you attach a sample?

3. ## Re: Counting total numbers (Excel 2003)

I tried using the "countif" function but it only works for ranges and criteria on one sheet (Ex- =COUNTIF(B3:B1000,2) ). When I try multiple sheets it returns #VALUE! error. Is there a similar function that works for multiple sheets?

4. ## Re: Counting total numbers (Excel 2003)

However tedious it may be, you'll have to use

=COUNTIF('H 26-05'!B3:B1000,2)+COUNTIF('E 27-05'!B3:B1000,2)+COUNTIF('F 28-05'!B3:B1000,2)

5. ## Re: Counting total numbers (Excel 2003)

Lastcall:

As an alternative, if each of your sheets has the COUNTIF in the same cell (I5 in sheet 1) then you can sum 'through' the sheets on sheet 1 with:

=SUM('H 26-05:F 28-05'!I5)

The number of sheets doesn't matter, as long as you reference the first and last sheets.

(Note the single quote (') marks enclosing the sheet range, because of non-alphanumeric characters in the sheet names.)

Tony.

6. ## Re: Counting total numbers (Excel 2003)

thanks for the help, it made it easier for me.

7. ## Re: Counting total numbers (Excel 2003)

Thanks for the Help, that also got me the results that i was looking

8. ## Re: Counting total numbers (Excel 2003)

List your sheet names ina range and name that range. In this example, I've named it "sheets" without the quotes. Then use,

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!B3:B100 "),2))

Another option with the Morefunc addin is,

=COUNTIF.3D(Sheet1:Sheet17!\$B\$3:\$B\$100,2)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•