Countif function with multiple sheets

[christianbiker]

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...

 

[Sandeep Warrier]

You could try

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

should work

or maybe

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

 

[jbeaucaire]

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.

http://www.mrexcel.com/forum/showthread.php?t=36082#4

I used it here to help another query:
http://www.mrexcel.com/forum/showthread.php?p=1824312#3

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)

 

[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?

 

[Sandeep Warrier]

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

 

[christianbiker]

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

 

[jbeaucaire]

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

Excel Workbook
	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
Day25

Excel Workbook
	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

Excel Workbook
	K	L
4	3	6
5	5	2
Sheet1

 

[christianbiker]

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.

Excel Workbook
	E
8	Level 2
9	Level 2
10	XXX
11	XXX
12	Level 2
13	Level 2
14	Level 1: 5-7 axle
Day 26

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:

Excel Workbook
	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
Monthly Summary

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

 

[Aladin Akyurek]

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.

 

[christianbiker]

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)