+ Reply to Thread
Results 1 to 11 of 11

COUNTIF / SUMS where distinct Criteria is MET

  1. 01-24-2020, 11:43 AM #1
    rz6657
    rz6657 is offline
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    372

    COUNTIF / SUMS where distinct Criteria is MET

    I've doing the count/sum manually all of 2019 as I haven't been able to figure out how to automatically count / sum each distinct criteria.

    There are 3 different sets and each one differs. I tried using the COUNTIF and SUMIF but I've not been successful. In the attached file, I've outlined each criteria for each row in the Status tab that counts / sums from the Working Detail tab (this data is dynamic and having to count/sum daily is tiresome). The current count/sum in the table is what I'm trying to achieve for today's exercise. Thanks in advance...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-24-2020, 11:44 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,610

    Re: COUNTIF / SUMS where distinct Criteria is MET

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 01-24-2020, 12:06 PM #3
    rz6657
    rz6657 is offline
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    372

    Re: COUNTIF / SUMS where distinct Criteria is MET

    Done. Thanks...
    Reply With QuoteRegister To Reply
  4. 01-24-2020, 12:36 PM #4
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: COUNTIF / SUMS where distinct Criteria is MET

    this works for row 2... =COUNTIFS('Working Detail'!$G:$G,R$1,'Working Detail'!$H:$H,0,'Working Detail'!$I:$I,">"&0)
    working on the others.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  5. 01-24-2020, 12:39 PM #5
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: COUNTIF / SUMS where distinct Criteria is MET

    this works for row 4 in the status tab... =COUNTIFS('Working Detail'!$G:$G,R$1,'Working Detail'!$H:$H,">"&0,'Working Detail'!$I:$I,">"&0)
    not sure what you mean by sum for rows 6 and 5 but will reread the requirements again. And will work on row 15.
    Reply With QuoteRegister To Reply
  6. 01-24-2020, 12:43 PM #6
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: COUNTIF / SUMS where distinct Criteria is MET

    this works for row 15 test IDs... =COUNTIFS('Working Detail'!$G:$G,R$1,'Working Detail'!$H:$H,">"&0,'Working Detail'!$I:$I,0)
    sorry to do this piecemeal, also trying to do my work at work.
    Reply With QuoteRegister To Reply
  7. 01-24-2020, 12:55 PM #7
    rz6657
    rz6657 is offline
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    372

    Re: COUNTIF / SUMS where distinct Criteria is MET

    Hi Sambo kid and thanks for helping me on this...

    As for the sum for rows 3, 5 and 16:
    So, while meeting criteria for R2 (sum the Test 1 Cases on column I in the Working Detail tab), S2 (sum the Test 1 Cases on column I in the Working Detail tab) and U2 (sum the Test 1 Cases on column I in the Working Detail tab)
    So, while meeting criteria for R4 (sum the Test 1 Cases on column I in the Working Detail tab), S4 (sum the Test 1 Cases on column I in the Working Detail tab) and U4 (sum the Test 1 Cases on column I in the Working Detail tab)
    So, while meeting criteria for R15 (sum the Test 1 Cases on column I in the Working Detail tab), S15 (sum the Test 1 Cases on column I in the Working Detail tab) and U15 (sum the Test 1 Cases on column I in the Working Detail tab)

    Hope this clarifies things--
    Reply With QuoteRegister To Reply
  8. 01-24-2020, 12:56 PM #8
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: COUNTIF / SUMS where distinct Criteria is MET

    this for row 3, test 1 cases...=SUMPRODUCT(('Working Detail'!$G:$G=R$1)*('Working Detail'!$H:$H=0)*('Working Detail'!$I:$I>0),'Working Detail'!$I:$I)
    this is for row 16, test 2 cases... =SUMPRODUCT(('Working Detail'!$G:$G=R$1)*('Working Detail'!$H:$H>0)*('Working Detail'!$I:$I=0),'Working Detail'!$H:$H)
    think I got them all for you.
    Reply With QuoteRegister To Reply
  9. 01-24-2020, 01:16 PM #9
    rz6657
    rz6657 is offline
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    372

    Re: COUNTIF / SUMS where distinct Criteria is MET

    Thx, working good. I think the only 1 left is row 5 where it has 2 conditions and sums both columns H and I once criteria is met---
    Reply With QuoteRegister To Reply
  10. 01-24-2020, 01:47 PM #10
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: COUNTIF / SUMS where distinct Criteria is MET

    this should take care of your row 5 values...
    =SUMIFS('Working Detail'!$H:$H,'Working Detail'!$G:$G,R$1,'Working Detail'!$H:$H,">"&0,'Working Detail'!$I:$I,">"&0)+SUMIFS('Working Detail'!$I:$I,'Working Detail'!$G:$G,R$1,'Working Detail'!$H:$H,">"&0,'Working Detail'!$I:$I,">"&0)
    Reply With QuoteRegister To Reply
  11. 01-24-2020, 02:55 PM #11
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: COUNTIF / SUMS where distinct Criteria is MET

    Glad I could help, AND thank you for the rep!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 Distinct with a second Criteria
    By locopete999 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2019, 11:37 AM
  2. How to search sums of table, with multiple criteria AND less criteria than needed
    By HereComesTheBoom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-25-2018, 02:42 PM
  3. Countif Distinct and Criteria HELP!!!
    By deadlyliquidxxx in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2015, 04:22 PM
  4. How to make Endless formula countif and sums??
    By Da-Kaiser in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-10-2013, 04:42 PM
  5. Distinct COUNTIF
    By TheNewUnion in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-19-2012, 06:27 AM
  6. Formula - Count distinct / Countif??
    By Africa in forum Excel General
    Replies: 3
    Last Post: 02-29-2012, 11:24 AM
  7. Multiple Countif Sums
    By Darran in forum Excel General
    Replies: 2
    Last Post: 06-01-2006, 06:30 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1