+ Reply to Thread
Results 1 to 4 of 4

Sumproduct vs countifs or Sumproduct + countifs

  1. #1
    Registered User
    Join Date
    01-31-2018
    Location
    Bathurst, NB
    MS-Off Ver
    2010
    Posts
    37

    Sumproduct vs countifs or Sumproduct + countifs

    I'm trying to merge formulas but one is a Countifs function and the other is a Sumproduct.

    The following Sumproduct formula returns a count of rows in which the date in D is greater than the date in P, providing there is a date in each D and P.

    Please Login or Register  to view this content.
    I also use the following Countifs to return a count of rows with "Initial*" in E and no value in P. (There are formulas in P but it does not always return a date value)
    Please Login or Register  to view this content.
    Now, I'd need this last formula to also count rows with "Initial*" in E with a date value in D that is greater than the date in P...

    This formula gives me a values error:
    Please Login or Register  to view this content.
    And this formula returns a 0:
    Please Login or Register  to view this content.
    Can anyone spot where I'm going astray?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,894

    Re: Sumproduct vs countifs or Sumproduct + countifs

    =SUMPRODUCT(--(Master!E3:E9999="Initials*"),--(Master!D3:D9957>0)*(Master!P3:P9957>0)*(Master!D3:D9957>=Master!P3:P9957))

    the ranges are not all the same...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,894

    Re: Sumproduct vs countifs or Sumproduct + countifs

    =COUNTIFS(Master!E:E,"Initial*",Master!D:D,">"&Master!P:P)

    Syntax was wrong....

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Sumproduct vs countifs or Sumproduct + countifs

    Sumproduct requires all the ranges to be of equal dimension.
    You have one range E3:E9999, while the others are D3:d9957

    The countifs can't do that, just because it can't. It can only search a range for a single criteria.

    So it needs to be the sumproduct.
    But one other issue is that sumproduct can't use the wildcard * like countifs can.

    And pick one method of coercion, either the -- or *, don't mix them up unless necessary for other reasons.

    Try
    =SUMPRODUCT(--(LEFT(Master!E3:E9999,8)="Initials"),--(Master!D3:D9999>0),--(Master!P3:P9999>0),--(Master!D3:D9999>=Master!P3:P9999))

+ 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. Help with COUNTIFS and/or SUMPRODUCT
    By bayouwxman in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 01-31-2018, 02:15 PM
  2. [SOLVED] Sumproduct to replace countifs as countifs don't work on external source reference
    By KrishnaSagar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2017, 02:33 AM
  3. [SOLVED] Sumproduct + countifs
    By toci in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 03-27-2016, 04:09 PM
  4. [SOLVED] CountIfS and SUMPRODUCT need help
    By rschoenb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-12-2014, 11:21 PM
  5. [SOLVED] Sumproduct/countifs
    By mahat in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-21-2014, 10:30 PM
  6. COUNTIFS and SUMPRODUCT help
    By Stacy1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-19-2013, 02:35 PM
  7. Using SUMPRODUCT instead of COUNTIFS
    By SymphonyTomorrow in forum Excel General
    Replies: 12
    Last Post: 11-18-2011, 05:01 PM

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