Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: SUMIF Based on Two Criteria?

    My task to to add totals at the bottom of and Excel report based on 2 critera. Here is an example of columns A, B and C:
    Jones A 50.00
    Jones B 100.00
    Jones A 35.00
    So if Jones is in column A and "A" is in column B, I need to sum the value in column C, which would total 85.00.
    If this is possible, I would create the formula in Visual Basic. The function SUMIF seems to only work with 1 criteria.

    The summed results for all 'A's and all 'B's are each displayed in separate columns.

    Jerry

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. The SUMPRODUCT function works with more than one criteria.

    =SUMPRODUCT(($A$1:$A$20="Jones")*($B$1:$B$20="A"), $C$1:$C20)

    Just remember that all three column lengths need to be the same (length); and the length cannot be the entire column. Dynamic named ranges work well for this
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Two Other ways to do this rather easily. (1) Use a subtotal formula. I like this the best because it is very easy to set up using the "AutoSum" Button on the standard toolbar. If you have your list filtered then click the AutoSum button it will create a SubTotal formula. If you change the filter settings the total will reflect the filtered data. Looks like this:
    =SUBTOTAL(9,C16:C21)

    Another way is with an Array Formula it is simalar to the SumProduct formula Shades suggested. To create an Array Formula you have to press keys Ctl+Shft+Enter Excel will apply curly brackets to the properly configured formula making it an array formula. Looks like this:
    {=SUM((A2:A6="Jones")*( B2:B6 = "A") * (C2:C6))}
    Attached Thumbnails Attached Thumbnails ExcelSubTotal.gif   ExcelArrayFormula.gif  
    Last edited by savbill; 09-02-07 at 23:59.
    ~

    Bill

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thank you for those two suggestions. I went with the array formula. This is done in VBA using Selection.FormulaArray =.
    Jerry

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    Sure I've never used 'FormulaArray' function in VB like this but it works. You're going to have to manage the quotes. Single quotes wont work. This is what I did for a quick test.

    Selection.FormulaArray = "=SUM((A2:A6=" & chr(34) & "Jones" & chr(34) & ")*( B2:B6 = " & chr(34) & "A" & chr(34) & ")*(C2:C6))"
    ~

    Bill

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Just as a note: Array formulas in VBA are better done using R1C1 notation than A1 notation. See chapter 6 of the book VBA and Macros for Microsoft Excel by Bill Jelen and Tracy Syrstad.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Jerrydal

    I support Shades solution using the SUMPRODUCT function. Although I write it a little differently it is the most powerful function in Excel and it is easy to use.

    You can see an example on how to use SUMPRODUCT with addresses rather than hard coded value at:

    http://www.excel-vba.com/excel-13-fo...sumproduct.htm
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  8. #8
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thank you for the link to SUMPRODUCT. I will study the information. Here is an example of the array formula that I am using in VBA, and it does work for me. In this example the first blank row was determined to be row #5039.

    Code:
    {=SUM(IF($B2:$B5038=$B5039,IF($Y2:$Y5038="A",1,0),0))}
    What I have done is capture a list of unique names in column B into an array. Column B is not the primary sort and names in column B are scattered throughout the list. Then at the first blank row at the end at column B I fill in each name, let's say "Jones". The formula looks for "Jones" in columns B, cells 2-5038, and if found, does a check in column Y, cells 2-5038 for the letter "A". If both conditions are met, the value of 1 is summed otherwise the value is 0. This type of counting and summing is done for some of the other columns, then the row is copied as values into a temporary worksheet, and the loop continues until all names in the array have been used to calculate totals for each name.

    I entered the array formulas only one time and only change the value in B5039, filling in the name from the array, to get the summary totals. Row 5039 gets cleared after totals are calculated for every name.

    The name and new totals in the temp worksheet are sorted and copied to an area below the report worksheet.

    This array formula process allows me to add sums and counts by values in certain columns, and in some cases pairs of columns (as in the above example) at the bottom of a list.
    Jerry

  9. #9
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    JerryDal

    If it works it's good.

    I have used the array format of SUM before I discovered SUMPRODUCT. Basically they do the same thing.

    My point is that with SUMPRODUCT you are on a path of discoveries.

    Why I dropped the array SUM:

    - If you forget to use SHIFT/CTRL/Enter you still get a result (0) and it is dangerous.
    - If you try to modify one of many array formulas you can't
    - It is so much easier to share with others about SUMPRODUCT than about array SUM
    - With SUMPRODUCT you can sum, count, calculate averages and even do lookups with many criteria

    The only function/formula that I now use is INDEX/MATCH to lookup for non-numerical values with many criteria.

    Array formulas are fun and you can spend the rest of your career finding things that you can do with the array format of almost any function. But as any other things they have limits and you wont find much help on the subject and you wont be able to do the most important thing which is share your knowledge with as many other users as possible.

    A little advise from an old man who found the real pleasure in life: sharing
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  10. #10
    Join Date
    Feb 2004
    Posts
    533
    Jerry, those are some good reasons to use SumProduct and good examples. I will keep this in mind for future use.
    ~

    Bill

  11. #11
    Join Date
    Jun 2012
    Posts
    1

    SUMProduct

    Hi guys,

    Thank you for this. This thread was able to solve a good deal of my problems. I have 2 questions, which I am still struggling with, and I think sumproduct migth be the way to go. I have the following big data set:

    Columns:
    A; B; C; D;
    Fund; Product; MRR $; Users;
    F1 P1 500 1
    F2 P1 500 2
    F3 P2 1000 3
    F1 P2 1000 4
    F2 P2 1000 5

    I'm trying to do the following:
    1. Sum the combined MRR value for all funds that have both P1 and P2; example - F1 and F2 have both P1 and P2, so I want to show a MRR sum of $3,000.
    2. Show the total number of funds that have both P1 and P2; in this case 2
    3. List all funds (individually), which have both P1 and P2, together with the number of users. Manually filtering those + pivot table could perhaps do the job, but any automation solution would be great.

    Greatly appreciate any sughestions. Many thanks,

    Ivan

Posting Permissions

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