+ Reply to Thread
Results 1 to 2 of 2

COUNTIFS and INDIRECT Function

Hybrid View

  1. #1
    Registered User
    Join Date
    04-07-2014
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    4

    COUNTIFS and INDIRECT Function

    Hi all,

    Because countif cannot be used across multiple tabs, I'm using the following workaround where "MySheets" is the range of tabs and E8 is the cell I want "counted".

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented"))

    This works perfectly, except that I really need COUNTIFS... I want Excel to count either "Fully implemented" or "Partially implemented" when in E8. I cannot get the formula to work and keep getting an error message. Can you help with the syntax?

    =SUMPRODUCT(COUNTIFS(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented",(INDIRECT("'"&MySheets&"'!E8"),"Partially implemented")))


    Thank you!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNTIFS and INDIRECT Function

    Try it like this...

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!E8"),{"Fully implemented","Partially implemented"}))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. [SOLVED] Countifs,Indirect Function and Vlookup combined
    By Yari1986 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 11:19 AM
  2. [SOLVED] Using COUNTIFS function
    By Karnik in forum Excel General
    Replies: 2
    Last Post: 11-12-2012, 01:01 AM
  3. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  4. COUNTIFS function
    By RM Rilke in forum Excel General
    Replies: 11
    Last Post: 09-30-2010, 09:16 PM
  5. CountIFS using an OR function
    By Maudise in forum Excel General
    Replies: 6
    Last Post: 08-09-2010, 10:47 AM

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