+ Reply to Thread
Results 1 to 23 of 23

Countifs or Sumifs

  1. #1
    Registered User
    Join Date
    11-25-2014
    Location
    Rochester, NY
    MS-Off Ver
    2010
    Posts
    8

    Countifs or Sumifs

    Hello,

    I am a novice with excel and I am trying to get a formula to work with array and criteria.

    The formula I am using is:
    =SUM(COUNTIFS('Kevin Accounts'!$B$2:$B$560,">="&'Formula Data'!A2,'Kevin Accounts'!$B$2:$B$560,"<="&'Formula Data'!A6,'Kevin Accounts'!$C$2:$C$560,{"Live with Support","100% complete},'Kevin Accounts'!$I$2:$I$560,,{"HT RED","HT Halifax","Legacy Diversity","HT AURORA 1 OR 2","DATA ENTRY ROCH","HT Roch","WELLS/BANK","NYS","STORE KIT","GOVERNMENT/FED"},'Kevin Accounts'!$J$2:$J$560,{"Implementation","Restructure"))

    This is a long formula and I am not sure of any other way to do it. I will also need a second formula to count a specific dollar amount that falls with in this criteria as well.

    Any help would be greatly appreciated. Thank you.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Countifs or Sumifs

    I'd recommend liberal use of the Name Manager under the Formulas tab to shorten your formulas considerably.

    For example, you can set the word "Accounts" to represent: ={"HT RED","HT Halifax","Legacy Diversity","HT AURORA 1 OR 2","DATA ENTRY ROCH","HT Roch","WELLS/BANK","NYS","STORE KIT","GOVERNMENT/FED"}

    Also, use Alt+Enter to organize your formulas. It will make them easy to troubleshoot and edit.

    Cleaned up with linebreaks and using just three defined names you could end up with:

    Please Login or Register  to view this content.
    Which looks fine.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Countifs or Sumifs

    However, if you're beginning to branch out into arrays I'd highly recommend you use SUMPRODUCT.

    It doesn't require the use of CSE braces unless you nest a few certain expressions within it, and it does everything COUNTIF, SUMIF and more.

    Count of cells where the criteria are met:

    Please Login or Register  to view this content.
    Now let's pretend you have your dollars out in Column K and you want to sum that where criteria are met:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-25-2014
    Location
    Rochester, NY
    MS-Off Ver
    2010
    Posts
    8

    Re: Countifs or Sumifs

    Hello,

    Thanks for your quick response.

    Using this suggestion it returned 0 as the result.

    =SUM(COUNTIFS('Kevin Accounts'!$B$2:$B$560,">="&'Formula Data'!A2,
    'Kevin Accounts'!$B$2:$B$560,"<="&'Formula Data'!A6,
    'Kevin Accounts'!$C$2:$C$560,Support,
    'Kevin Accounts'!$I$2:$I$560,Accounts,
    'Kevin Accounts'!$J$2:$J$560,Implementation))

    Using this suggestion I received #N/A as the result

    =SUMPRODUCT(('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2)*
    ('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6)*
    ('Kevin Accounts'!$C$2:$C$560=Support)*
    ('Kevin Accounts'!$I$2:$I$560=Accounts)*
    ('Kevin Accounts'!$J$2:$J$560=Implementation))

    Thanks!
    Last edited by RalstonT14612; 11-25-2014 at 12:09 PM. Reason: Correction

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Countifs or Sumifs

    Did you define the ranges with those names?

  6. #6
    Registered User
    Join Date
    11-25-2014
    Location
    Rochester, NY
    MS-Off Ver
    2010
    Posts
    8

    Re: Countifs or Sumifs

    I apologize I am not sure what you mean about define? I am still very new to this formula function. I want it to look at all information with this list (Support, Accounts, Implementation).

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Countifs or Sumifs

    Click the Formulas Tab.

    Click Name Manager.

    Here you can make a word equal to a range or a set of variables


    For example, you can set PrettyPinkUnicorns to equal =A1:A50
    And then whenever you want to refer to A1:A50, you could just type PrettyPinkUnicorns.

    Let's take your first multicriteria: {"Live with Support","100% complete}

    You can create a defined name in the Name Manager called SadPanda, set equal to ={"Live with Support","100% complete}

    Now instead of having to type out {"Live with Support","100% complete} every time, you can just put SadPanda.
    As an added bonus, if that criteria changes, you just update the defined name and it changes for every formula

  8. #8
    Registered User
    Join Date
    11-25-2014
    Location
    Rochester, NY
    MS-Off Ver
    2010
    Posts
    8

    Re: Countifs or Sumifs

    I did sent a range for each list.

    example:
    Support is Live with Support and 100% complete

    Accounts is: HT RED, HT Halifax, Legacy Diversity,HT AURORA 1 OR 2,DATA ENTRY ROCH,HT Roch,WELLS/BANK,NYS,STORE KIT,GOVERNMENT/FED

    I have attached my spread sheet. I added the list to the formula tab.
    Attached Files Attached Files
    Last edited by RalstonT14612; 11-25-2014 at 02:19 PM. Reason: adding attachment

  9. #9
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Countifs or Sumifs

    Can you attach a small workbook sample so I can see what's wrong?

  10. #10
    Registered User
    Join Date
    11-25-2014
    Location
    Rochester, NY
    MS-Off Ver
    2010
    Posts
    8

    Re: Countifs or Sumifs

    I attached the workbook call Kevin- Tracker 2015

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Countifs or Sumifs

    I apologize, but after a few hours of research I am unable to find any way in which a named range of more than one cell can be applied as a criteria.

    I will see if I can trouble one of the old masters for some advice.


    Barring a more elegant solution, I'd run with the SUMPRODUCT version of your original.

    COUNT
    Please Login or Register  to view this content.
    SUM OF H
    Please Login or Register  to view this content.

  12. #12
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Countifs or Sumifs

    Hi.

    I won't unfortunately have time to look into the specifics of your question right now, though perhaps you could read this in the meantime:

    http://excelxor.com/2014/09/28/count...iteria_ranges/

    It appears that you are under the impression that you can enter as many criteria - where those criteria are intended as "OR" criteria - for as many different ranges as you wish using COUNTIFS, which is not necessarily so.

    If someone hasn't resolved your query by tomorrow then I'll take another look.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

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

    Re: Countifs or Sumifs

    Quote Originally Posted by XOR LX View Post
    It appears that you are under the impression that you can enter as many criteria - where those criteria are intended as "OR" criteria - for as many different ranges as you wish using COUNTIFS, which is not necessarily so.
    +1
    I'm only aware of being able to do 2 sets of {or} criteria in a sum/countIFs formula.
    AND, those 2 criteria arrays need to be transposed of each other, 1 a column, the other a row..

    =SUM(COUNTIFS(range1,{a,b,c,d},range2,{1;2;3}))
    Notice one {array} is using comma seperators, the other is using semicolons.

    You can't do 3.

    I'd suggest
    =SUM(COUNTIFS(range1,{a,b,c}))+SUM(COUNTIFS(range2,{1,2,3}))+SUM(COUNTIFS(range3,{8,9,10}))

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Countifs or Sumifs

    Here's another one...

    Put all the criteria in cells...

    Data Range
    A
    B
    C
    1
    2
    Live with Support
    HT RED
    Implementation
    3
    100% complete
    HT Halifax
    Restructure
    4
    Legacy Diversity
    5
    HT AURORA 1 OR 2
    6
    DATA ENTRY ROCH
    7
    HT Roch
    8
    WELLS/BANK
    9
    NYS
    10
    STORE KIT
    11
    GOVERNMENT/FED
    12


    Then the formula becomes:

    =SUMPRODUCT(--('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2),--('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6),--ISNUMBER(MATCH('Kevin Accounts'!$C$2:$C$560,A2:A3,0)),--ISNUMBER(MATCH('Kevin Accounts'!$I$2:$I$560,B2:B11,0)),--ISNUMBER(MATCH('Kevin Accounts'!$J$2:$J$560,C2:C3,0)))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  15. #15
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Countifs or Sumifs

    This is what I spent hours trying to do. This is awesome.

    Quote Originally Posted by Tony Valko View Post

    Then the formula becomes:

    =SUMPRODUCT(--('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2),--('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6),--ISNUMBER(MATCH('Kevin Accounts'!$C$2:$C$560,A2:A3,0)),--ISNUMBER(MATCH('Kevin Accounts'!$I$2:$I$560,B2:B11,0)),--ISNUMBER(MATCH('Kevin Accounts'!$J$2:$J$560,C2:C3,0)))

  16. #16
    Registered User
    Join Date
    11-25-2014
    Location
    Rochester, NY
    MS-Off Ver
    2010
    Posts
    8

    Re: Countifs or Sumifs

    Hi Jonmo1,

    The first solution is working for me with 2 as you stated. I tried to do the 3 alternate and it started doubling and tripling the count for some reason. When I needed to do more than three I just kept the third criteria to a single text and then copied the formula for the second selection. If that makes sense.

  17. #17
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Countifs or Sumifs

    This returns a count of 2 for B13:

    Please Login or Register  to view this content.
    And this SUMS for H:

    Please Login or Register  to view this content.
    Attached Files Attached Files

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

    Re: Countifs or Sumifs

    Yep that makes sense..

    Basically sumif(1storcriteria)+sumif(2ndorcriteria)

  19. #19
    Registered User
    Join Date
    11-25-2014
    Location
    Rochester, NY
    MS-Off Ver
    2010
    Posts
    8

    Re: Countifs or Sumifs

    How would I make this work if I needed to use the criteria and counting $ amounts?

  20. #20
    Registered User
    Join Date
    11-25-2014
    Location
    Rochester, NY
    MS-Off Ver
    2010
    Posts
    8

    Re: Countifs or Sumifs

    I think you solved my problem! Thank you everyone, I was going insane!

  21. #21
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Countifs or Sumifs

    Hi,

    If you want to avoid helper cells, then this is the array formula (so need to ended with Ctrl-Shift-Enter) :
    (I put this array formula on Formula Data!L8 at attached file)

    Please Login or Register  to view this content.
    Regards
    Attached Files Attached Files
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  22. #22
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Countifs or Sumifs

    Quote Originally Posted by daffodil11 View Post
    This is what I spent hours trying to do. This is awesome.
    Put an example in your stash.

  23. #23
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Countifs or Sumifs

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ 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. Formula help - COUNTifs, or SUMifs or?
    By jomu84sa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-16-2014, 05:27 AM
  2. [SOLVED] COUNTIFS and SUMIFS formula help
    By jmcole in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2014, 07:56 PM
  3. [SOLVED] I can countifs a range but not sumifs
    By port in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-18-2014, 07:06 PM
  4. Need help in Excel SUMIFS + COUNTIFS
    By milehisweetipie in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-24-2013, 08:11 PM
  5. Sumifs and countifs
    By JohnGault82 in forum Excel General
    Replies: 3
    Last Post: 02-03-2011, 05:31 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