+ Reply to Thread
Results 1 to 2 of 2

Can't add criteria to SUM(COUNTIF(INDIRECT

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,259

    Can't add criteria to SUM(COUNTIF(INDIRECT

    Apologies if this has been covered before, but I can't find anything on the net?

    Formula in AU3 and AV3 on the attached sets a "fixed " range of non-contiguous columns to calculate how many night shifts and how many early/afternoon shifts match a string over two weeks.

    =SUM(COUNTIF(INDIRECT({"E4:K15","Q4:W15","AC4:AG15","AL4:AN15"}),"*SHELLS*"))

    I want to add two criteria to the formula, so it finds only the records where the Trading Period in COl D matches AS4 and AS5, and the Weeks in Col C match AT4 and AT5

    Col AX:BB show what the criteria should produce.

    Any solutions, suggestions or alternative welcome as ever.

    Ochimus

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,081

    Re: Can't add criteria to SUM(COUNTIF(INDIRECT

    If you can unmerge the header in row 1 and put the header like to

    Early Early Early Afternoon Afternoon Afternoon

    Please try at
    AU4
    =SUMPRODUCT((ISNUMBER(SEARCH("SHELLS",$E$4:$AQ$15))*($E$1:$AQ$1<>"Night")*($C$4:$C$15=$AT4)*($D$4:$D$15=$AS4)))

    AV4
    =SUMPRODUCT((ISNUMBER(SEARCH("SHELLS",$E$4:$AQ$15))*($E$1:$AQ$1="Night")*($C$4:$C$15=$AT4)*($D$4:$D$15=$AS4)))


    An easy way to fill the header
    Select E1:AP1 > unmerge > Press F5 > Special > Blank > Key-in = and press left arrow > Press Ctrl+Enter
    Last edited by Bo_Ry; 01-31-2019 at 12:13 AM.

+ 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] Count One Criteria Across Multiple Worksheets with SUMPRODUCT(COUNTIF(INDIRECT shows error
    By Tryin2Excel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-22-2017, 05:13 PM
  2. Replies: 8
    Last Post: 10-14-2014, 01:54 AM
  3. [SOLVED] problem with =COUNTIF((OFFSET(INDIRECT(CONCATENATE(("'Quality Audit Criteria'!$f$",MATCH(
    By nadiac2402 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-13-2014, 08:22 AM
  4. SUM, COUNTIF, and INDIRECT - Need help
    By bubbacheese in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2014, 10:49 AM
  5. [SOLVED] 2 data Range using COUNTIF with INDIRECT and MIN IF with INDIRECT,
    By david gonzalez in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-29-2013, 08:10 PM
  6. COUNTIF and INDIRECT
    By Simon Gahan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-21-2012, 05:52 AM
  7. Countif using an indirect in the criteria
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-05-2007, 09:57 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