+ Reply to Thread
Results 1 to 5 of 5

sumproduct to count multiple criteria (once per row)

  1. #1
    Registered User
    Join Date
    01-03-2015
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    55

    sumproduct to count multiple criteria (once per row)

    I'm nearly there but I'm stumped on the logic... (I've attached an example workbook with my problem, and my own attempt to fix it.)

    At first I used COUNTIFS, but I moved the same logic over to SUMPRODUCT as I wanted an array to be tested in the 3rd criterion.

    I'm looking for a formula to count the rows that:
    A) are >0 in column J
    B) are blank in column I
    C) have the sum of columns $C:H as >0

    what I've tried
    =SUMPRODUCT((J9:J99>0) * (I9:I99="") * ($C9:H99>0))

    which nearly gets it, BUT instead of counting the rows where the above criteria are all true (once per row), it instead counts rows multiple times if there are multiple non=blanks in the C:H range of that row...

    HELP!
    Thanks
    Attached Files Attached Files
    Last edited by MikeDeButts; 01-25-2022 at 07:17 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: sumproduct to count multiple criteria (once per row)

    Not sure what result you are expecting but try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: sumproduct to count multiple criteria (once per row)

    Please try

    =SUMPRODUCT((J$9:J$101>0)*(I$9:I$101="")*(MMULT((N(+$C$9:H$101)),{1;1;1;1;1;1})>0))
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-03-2015
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: sumproduct to count multiple criteria (once per row)

    yeah I thought wrapping it in SUM() would work too, but sadly not.
    ie it gives a result, but not the result i need...
    It was after trying to untangle why that didn't work that lead me to ask greater minds than mine!
    Thanks though
    Last edited by MikeDeButts; 01-25-2022 at 07:21 AM.

  5. #5
    Registered User
    Join Date
    01-03-2015
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: sumproduct to count multiple criteria (once per row)

    Thanks Bo_Ry

    MMULT worked a charm. That's exactly what i needed!

    (i had to edit it a little so the formula was copyable across the whole row (so the {1;1;1;1;1;1} needed to grow dynamically, for which i used the transposed array of 1s from the row headers being >0

    Thanks!

+ 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. Using SUMPRODUCT to count multiple criteria ignoring blank cells
    By azcueja33 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2020, 11:57 PM
  2. [SOLVED] Sumproduct & count if with multiple criteria
    By Jamidd1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-06-2018, 01:49 PM
  3. Replies: 4
    Last Post: 02-12-2016, 04:27 PM
  4. Replies: 16
    Last Post: 05-25-2015, 08:51 AM
  5. Count w/multiple criteria across multiple columns - SUMPRODUCT, MATCH?
    By MrHoohah in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2015, 05:23 PM
  6. Replies: 1
    Last Post: 02-21-2014, 09:09 PM
  7. [SOLVED] Need to use either COUNTIFS or SUMPRODUCT to count rows based on multiple criteria
    By erabinov in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-15-2013, 03:15 PM

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