Results 1 to 7 of 7

Thread: Sum countifs

  1. #1

    Sum countifs



    Register for a FREE account, and/
    or Log in to avoid these ads!

    =SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,1,'Jan 22-26'!E:E,2))

    I need this to read 1 OR 2 not 1 AND 2. There might be no 1's, there might be no 2's. I need it to count them if they're available.

    Essentially, If column A is "Andy Ryan", Column D is "Y", and Column E has a 1 and/or a 2, then SUM.
    Last edited by p45cal; 2018-02-01 at 10:19 PM. Reason: remove smilies

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Does this work?

    Code:
    =SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))
    or if that doesn't work for you try:

    Code:
    =SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,{1,2}))
    Last edited by NBVC; 2018-02-01 at 08:47 PM.


  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,733
    Articles
    0
    Excel Version
    365
    Something along the lines of:
    =SUMPRODUCT((A1:A20="Andy Ryan")*(D1:D20="Y"),(E1:E20=1)+(E1:E20=2))
    ?
    You can fill in the sheet references. Try not to use entire columns, they're very heavy on resources.
    Last edited by p45cal; 2018-02-01 at 11:06 PM.

  4. #4

    feels impossible

    Hey thanks! This worked!

    =SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D,"Y",'Jan 22-26'!E:E,"<=2"))

    I need to add to this though now. I need it to look at another number in the same column as E. If there are 3's, count them and divide the total of 1's and 2's by the 3's to get a percentage. Honestly, i have no clue how to get this.

    =SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D,"Y",'Jan 22-26'!E:E,"<=2"))

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Perhaps?


    Code:
    =SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))/SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,3))


  6. #6

    Yes!

    Quote Originally Posted by NBVC View Post
    Perhaps?


    Code:
    =SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))/SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,3))

    Thank you! This did work however, question: what if there are no 3's to count? Then it divides by 0 and i can't get a percent? It would technically be 100% if there are no 3's. Any idea how to make this work?

    THANK YOU!!!

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    You can use IFERROR:

    Code:
    =IFERROR(SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))/SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,3)),1)


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •