You could try
Countif("Sheet1Range",Criteria)+Countif("Sheet2Range",Criteria)+...
should work
or maybe
Sumproduct(--(sheet1range=criteria),--(sheet2range=criteria),.......)
This is a discussion on Countif function with multiple sheets within the Excel Questions forums, part of the Question Forums category; Can I use the "countif" function using a group of sheets as the range and the criteria on a separate ...
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...
I finally upgraded to Excel 2013...it was a good move!
You could try
Countif("Sheet1Range",Criteria)+Countif("Sheet2Range",Criteria)+...
should work
or maybe
Sumproduct(--(sheet1range=criteria),--(sheet2range=criteria),.......)
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)
Microsoft MVP 2010 - Excel
Jerry Beaucaire's Excel Tools
"Actually I *am* a rocket scientist." -- JB
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?
I finally upgraded to Excel 2013...it was a good move!
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
Last edited by Sandeep Warrier; Feb 26th, 2009 at 09:42 PM.
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
I finally upgraded to Excel 2013...it was a good move!
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
Spreadsheet Formulas
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
Microsoft MVP 2010 - Excel
Jerry Beaucaire's Excel Tools
"Actually I *am* a rocket scientist." -- JB
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
I finally upgraded to Excel 2013...it was a good move!
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)
Last edited by christianbiker; Feb 27th, 2009 at 02:07 PM.
I finally upgraded to Excel 2013...it was a good move!
Like this thread? Share it with others