Results 1 to 11 of 11
Thread: SUMIF Based on Two Criteria?

083007, 16:12 #1Registered User
 Join Date
 Jan 2002
 Location
 Bay Area
 Posts
 511
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

090107, 15:42 #2Registered User
 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 thisold, slow, and confused
but at least I'm inconsistent!
Rich
(retired Excel 2003 user, 3/28/2008)
How to ask a question on forums

090207, 22:55 #3Registered User
 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))}Last edited by savbill; 090207 at 22:59.
~
Bill

090407, 11:42 #4Registered User
 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

090407, 19:53 #5Registered User
 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

090507, 20:35 #6Registered User
 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

091207, 19:23 #7Registered User
 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.excelvba.com/excel13fo...sumproduct.htmA piece of data is like a brick
If you don't build anything with it
It is just a brick
www.excelexamples.com

091207, 20:19 #8Registered User
 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))}
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

091207, 20:41 #9Registered User
 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 nonnumerical 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: sharingA piece of data is like a brick
If you don't build anything with it
It is just a brick
www.excelexamples.com

091807, 19:13 #10Registered User
 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

061812, 07:49 #11Registered User
 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