+ Reply to Thread
Results 1 to 9 of 9

The SUMIF & COUNTIF Function

  1. #1
    Registered User
    Join Date
    06-04-2008
    Posts
    11

    The SUMIF & COUNTIF Function

    I have got a general question. I am sure this is possible. I am trying to use a function that is similar to the SUMIF & COUNTIF functions, but I want it to go one more level...example

    ---A--B--C
    1 cat 2 1
    2 dog 3 4
    3 cat 3 2
    4 cat 2 2
    5 dog 3 2
    6 cat 1 3
    7 dog 5 4
    8 cat 2 2
    9 cat 5 2

    If I am trying to sum column C that meet the criteria - Column A = "cat" and Column B = "2"...I know that if I wanted to sum Column B for cats, I could "=SUMIF(A:A, "cat", B:B)"...but how do I do one more level to sum if it meets two criteria?


  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    The SUMIF & COUNTIF Function

    With multiple criteria, the SUMPRODUCT function will do what you want.

    Try this:
    Please Login or Register  to view this content.
    Does that help?
    Post back if you have more questions.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    06-04-2008
    Posts
    11
    Thank you for replying. I must be having a hard time with this one, 'cause I tried your code and I also looked up a couple other threads with similar topics and maybe the data I am using is the problem...most likely, as in most cases, it is the user...me

    I tried these two lines of code...

    =SUMPRODUCT((Jan!B:B=Gas)*(Jan!G:G=1)*Jan!E:E)

    =SUMPRODUCT((Jan!B:B=Gas),(Jan!G:G=1),Jan!E:E)


    Here is what i am trying to do...I have created a workbook that is set up for expendures in an account. I have 12 worksheets, one for each month, then on the 13th worksheet I have my data output page. For the above equation...

    Jan!B:B...column B has various posts in it: gas, food, store, etc.
    Jan!G:G...column G has 5 numerical values: 1, 2, 3, 4, 5 (for gas these numbers refer to the car being used...iow, there are 5 different cars)
    Jan!E:E...column E is the amount listed in $0.00 format

    When I use the above equations, I keep getting the error "#NUM!"...when playing with the equation a bit, I get "#VALUE!" and "#NAME!" as well.

    I am looking for the following output...I want to sum the total cost of gas for car #1 in the month of January....

    What am I doing wrong!!!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    A few problems with your formulas.

    1. Text strings in conditions must be enclosed within double quotes....

    2. The second Sumproduct (with commas) must have an additional "trigger" to convert the TRUE/FALSE arrays to 1's and 0's...(e.g --, +0, *1, etc)

    3. Unless you are using Excel 2007, you cannot have whole column references with the Sumproduct() formula..

    Try, instead

    =SUMPRODUCT((Jan!B2:B1000="Gas")*(Jan!G2:G1000=1),Jan!E2:E1000)

    or

    =SUMPRODUCT(--(Jan!B2:B1000="Gas"),--(Jan!G2:G1000=1),Jan!E2:E1000)

    adjusting ranges to suit.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    The SUMIF & COUNTIF Function

    Actually, SUMPRODUCT is one of the functions that can reference an entire
    ROW, but not an entire COLUMN. Also, when you are using text in a formula,
    you need to enclose it with quotes. Otherwise, Excel looks for a range name.
    In your formula, Excel is trying to find a Named Range called: Gas.

    Please Login or Register  to view this content.
    Another alternative is to create Dynamic Range Names, which automatically
    expand and contract to accommodate the actual data. and reference them
    in your formulas. There's very good coverage of that topic at Debra
    Dalgleish's website:
    http://www.contextures.com/xlNames01.html#Dynamic

    Does that help?
    Post back with more questions.
    Last edited by Ron Coderre; 06-05-2008 at 09:00 AM.

  6. #6
    Registered User
    Join Date
    06-04-2008
    Posts
    11
    It is funny, i had the qoutation marks in my excel formula in the workbook, but when I typed it here I forgot to do it...the problem I had was identifying the range of cells being used. I like the idea of using A:A or B:B because it allows for additions at a later date which still can be accomodated into the formula...but I figure I will just start using the range of cell names, rather then the column name.

    THANKS SO MUCH...GOT IT TO WORK!!!

  7. #7
    Registered User
    Join Date
    06-04-2008
    Posts
    11
    One thing I forget to put in my original post...how do you do the some thing but for COUNTIF functions. As in the example above...

    How would I figure the number of transactions for gas for car #1.

  8. #8
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    The SUMIF & COUNTIF Function

    It's easier than you might think....
    Just remove the cost component from the formula:

    Something like this:
    Please Login or Register  to view this content.
    Does that help?

  9. #9
    Registered User
    Join Date
    06-04-2008
    Posts
    11
    Thanks Ron. Worked like a charm!

+ 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. Sort column when using countif & sumif functions
    By kzprivate in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-20-2008, 08:33 PM
  2. Sumif , Countif
    By anetsurfer in forum Excel General
    Replies: 1
    Last Post: 12-02-2007, 11:32 PM
  3. COUNTIF Function using more than one criterion
    By ary in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2007, 05:14 AM
  4. countif function or sumif
    By tipuser in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-08-2007, 12:51 PM
  5. Nesting a countif function
    By kevinr1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2007, 09:57 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1