Countif function with multiple sheets

Thanks:  0
Likes:  0

# Thread: Countif function with multiple sheets

1. ## Countif function with multiple sheets

Can I use the "countif" function using a group of sheets as the range and the criteria on a separate sheet or manually typed in?

I have tried and continue to get the #VALUE error.

OR

Can I do the same thing and perform the "countif" function by using a specific value as the way to count?

Thanks...

2. ## Re: Countif function with multiple sheets

You could try

Countif("Sheet1Range",Criteria)+Countif("Sheet2Range",Criteria)+...

should work

or maybe

Sumproduct(--(sheet1range=criteria),--(sheet2range=criteria),.......)

3. ## Re: Countif function with multiple sheets

If you search on this site for Countif and 3d, you get many hits. Here is a page where an addin function Countif3D can be used to do what you want. Follow the instructions carefully.

I used it here to help another query:

And here's an approach that uses INDIRECT to string together several sheetnames in a formula, no UDF required. It's an interesting approach:

=SUM(COUNTIF(INDIRECT("Sheet"&{2,3,4,5,6,7,8,9,10,11}&"!A1:E5"),"="&A2))
(Source)

4. ## Re: Countif function with multiple sheets

Thanks for the responses....

I am looking at yours Sandeep and by what you are suggesting I will need to do that for every sheet in the workbook and everytime I want to use it for a different criteria. I don't this I really want to do that.

I also tried this one jbeau (formula below) and it is showing a #REF! error.

=SUM(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K4))

Does anyone have a simpler solution that will hopefully work?

5. ## Re: Countif function with multiple sheets

Originally Posted by christianbiker
Thanks for the responses....

I am looking at yours Sandeep and by what you are suggesting I will need to do that for every sheet in the workbook and everytime I want to use it for a different criteria. I don't this I really want to do that.

I also tried this one jbeau (formula below) and it is showing a #REF! error.

=SUM(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K4))

Does anyone have a simpler solution that will hopefully work?
You could put the criteria in certain cells and give reference of those cells in the formula. Then when you need to change the criteria just change those cell values.

Also, if you use a utility called ASAP utilities, it is possible to index all worksheet names in a single sheet. You could use this index sheet as reference for the sheet names and you dont have to type individual names.

Per Jbeaucaire's formula...
You havent done ,"="&K4

Maybe thats why you are getting an error

6. ## Re: Countif function with multiple sheets

thanks sandeep...i did try the formula formate you suggested ("="&K4) and it did not work. could you give me an idea of what your suggested formula may look like. i have 31 sheets to use and pull the same info from each. i am not quite understanding how to set up your formula.

thanks

7. ## Re: Countif function with multiple sheets

You formula works for me exactly.
=SUM(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K4))
Day25

 * E 8 1 9 2 10 3 11 4 12 1 13 2 14 3 15 4 16 1 17 2 18 3 19 4 20 5

Day26

 * E 8 1 9 2 10 3 11 4 12 1 13 2 14 3 15 4 16 1 17 2 18 3 19 4 20 5

Sheet1

 * K L 4 3 6 5 5 2

 Cell Formula L4 =SUM(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K4)) L5 =SUM(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K5))

Excel tables to the web >> Excel Jeanie HTML 4

8. ## Re: Countif function with multiple sheets

ok...thanks for the information. i think i may not have explained what i need properly.

hopefully i will do so now.

part a:
i have 31 different sheets (which are used as days of months...31 being the maximum number in a month). there is a list that a user selects from in order to populate the cells in a specific range. although there is a set list that is identical on each of the days and each of the cells in that range, the contents will differ based on what the user actually did that day and selects from the list. below is an example of what a day may look like. this can be different each of the 31 days.

Day 26

 * E 8 Level 2 9 Level 2 10 XXX 11 XXX 12 Level 2 13 Level 2 14 Level 1: 5-7 axle

Excel tables to the web >> Excel Jeanie HTML 4

part b:
on another sheet i would like to create a summary total of the 31 days based on the each of the list items that the user selected from as shown below:

Monthly Summary

 * J 4 Level 1: 2 axle 5 XXX 6 XXX 7 Level 1: 5-7 axle 8 Level 1: 8+ axle 9 Level 1 Bus 10 Level 1 MC 11 Level 1 PDP 12 Level 2 13 Level 2 MC 14 Level 3 15 Level 3 MC 16 Level 4 17 Level 4 MC 18 Level 4 Sch. Bus 19 Level 5 20 XXX 21 Level 5 Sch. Bus

Excel tables to the web >> Excel Jeanie HTML 4

so...if throughout the 31 days the user has done 25 "Level 1: 5-7 axle" items i would like the total to be shown to the right of the list where is says "Level 1: 5-7 axle" on the summary sheet.

this is what i would like to do as a minimum however if at all possible i would actually like to combine the totals from the 31 days for each list item that has common elements (i.e. "Level 1" or "Level 2" instead of "Level 1: 5-7 axle"

i really hope someone can help me.

thanks

9. ## Re: Countif function with multiple sheets

Originally Posted by christianbiker
Thanks for the responses....

I am looking at yours Sandeep and by what you are suggesting I will need to do that for every sheet in the workbook and everytime I want to use it for a different criteria. I don't this I really want to do that.

I also tried this one jbeau (formula below) and it is showing a #REF! error.

=SUM(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K4))

Does anyone have a simpler solution that will hopefully work?
Two observations...

[1] This formula must be confirmed with control+shift+enter, not just enter. If this is annoying to you, replace SUM with SUMPRODUCT.

[2] You'll get a #REF! error if a sheet which is referred to does not exist.

10. ## Re: Countif function with multiple sheets

thanks...

i have previously done both of your suggestions, excluding the sumproduct function, and continue to receive the same error. this is a couple of the formulas i have tried:

{=SUMPRODUCT(COUNTIF(INDIRECT("Day "&{25,26}&"!E8:E20"),K4))}
{=SUMPRODUCT(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K4))}
{=SUMCOUNTIF(INDIRECT("Day "&{25,26}&"!E8:E20"),K4))}
{=SUM(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K4))}

this is the original formula that i started with and received #VALUE! error:
=COUNTIF('Day 1:Day 31'!E8:E24,'Monthly Summary'!K4)

## User Tag List

#### Posting Permissions

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