+ Reply to Thread
Results 1 to 3 of 3

Count One Criteria Across Multiple Worksheets with SUMPRODUCT(COUNTIF(INDIRECT shows error

  1. #1
    Registered User
    Join Date
    02-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    21

    Count One Criteria Across Multiple Worksheets with SUMPRODUCT(COUNTIF(INDIRECT shows error

    The workbook has 23 worksheets, 1:23, where data is entered. Column B in each worksheet from B10:B133 is a named range called Received. When an item is received the receipt date is entered in column B. In that same workbook is a worksheet with formulas to count various totals for the month. I have not been able to write the below formula properly to count all cells with a date in the named range Received. The various ways I have written the formula have resulted in either a #NAME? or #REF! error. I have reviewed various sites regarding using this formula and I believe I am not placing a " or & correctly in the formula.

    The formula is =SUMPRODUCT(COUNTIF(INDIRECT('1:23'!Received),">1"))

    Any help you can provide will be greatly appreciated.

    Thank you.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Count One Criteria Across Multiple Worksheets with SUMPRODUCT(COUNTIF(INDIRECT shows e

    if this way
    =SUMPRODUCT(COUNTIF(INDIRECT("'"&row(1:23)&"'!B10:B133"),">1"))

  3. #3
    Registered User
    Join Date
    02-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    21

    Re: Count One Criteria Across Multiple Worksheets with SUMPRODUCT(COUNTIF(INDIRECT shows e

    EXCELLENT! That worked. Thank you for your prompt response.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 10-14-2014, 01:54 AM
  2. [SOLVED] Excel Novice - attempting to use SUMPRODUCT/SUMIF/INDIRECT but cell shows #REF
    By mbasi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-17-2013, 05:44 AM
  3. [SOLVED] countif or sumproduct to count if criteria set
    By koi in forum Excel General
    Replies: 4
    Last Post: 07-17-2012, 06:38 PM
  4. Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria
    By robgardner15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2011, 02:35 AM
  5. Replies: 6
    Last Post: 03-25-2010, 02:15 PM
  6. Multiple criteria countif or sumproduct
    By jmerre in forum Excel General
    Replies: 9
    Last Post: 09-16-2009, 03:09 PM
  7. countif/sumproduct on multiple criteria
    By Phil.M in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2005, 02:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1