Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Countif function with multiple sheets

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

  1. #1
    Board Regular
    Join Date
    Feb 2006
    Posts
    315

    Default 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...
    I finally upgraded to Excel 2013...it was a good move!

  2. #2
    Board Regular
    mmmm Pizza
    Sandeep Warrier's Avatar
    Join Date
    Oct 2008
    Location
    Mumbai, India
    Posts
    2,660

    Default 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. #3
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,745

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

    COUNTIF across multiple worksheets

    I used it here to help another query:
    http://www.mrexcel.com/forum/excel-questions/368079-spreading-text-list-bingo-game.html#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

  4. #4
    Board Regular
    Join Date
    Feb 2006
    Posts
    315

    Default 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?
    I finally upgraded to Excel 2013...it was a good move!

  5. #5
    Board Regular
    mmmm Pizza
    Sandeep Warrier's Avatar
    Join Date
    Oct 2008
    Location
    Mumbai, India
    Posts
    2,660

    Default Re: Countif function with multiple sheets

    Quote Originally Posted by christianbiker View Post
    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
    Last edited by Sandeep Warrier; Feb 26th, 2009 at 09:42 PM.

  6. #6
    Board Regular
    Join Date
    Feb 2006
    Posts
    315

    Default 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
    I finally upgraded to Excel 2013...it was a good move!

  7. #7
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,745

    Default Re: Countif function with multiple sheets

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

    *E
    81
    92
    103
    114
    121
    132
    143
    154
    161
    172
    183
    194
    205



    Day26

    *E
    81
    92
    103
    114
    121
    132
    143
    154
    161
    172
    183
    194
    205



    Sheet1

    *KL
    436
    552

    Spreadsheet Formulas
    CellFormula
    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

  8. #8
    Board Regular
    Join Date
    Feb 2006
    Posts
    315

    Default 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
    8Level 2
    9Level 2
    10XXX
    11XXX
    12Level 2
    13Level 2
    14Level 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
    4Level 1: 2 axle
    5XXX
    6XXX
    7Level 1: 5-7 axle
    8Level 1: 8+ axle
    9Level 1 Bus
    10Level 1 MC
    11Level 1 PDP
    12Level 2
    13Level 2 MC
    14Level 3
    15Level 3 MC
    16Level 4
    17Level 4 MC
    18Level 4 Sch. Bus
    19Level 5
    20XXX
    21Level 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!

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,677

    Default Re: Countif function with multiple sheets

    Quote Originally Posted by christianbiker View Post
    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. #10
    Board Regular
    Join Date
    Feb 2006
    Posts
    315

    Default 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)
    Last edited by christianbiker; Feb 27th, 2009 at 02:07 PM.
    I finally upgraded to Excel 2013...it was a good move!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com