# Thread: SUMIF Based on Two Criteria?

1. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513

## 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. Registered 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 this

3. Registered 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; 09-02-07 at 23:59.

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

5. Registered 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))"

6. Registered 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.

7. Registered User
Join Date
Dec 2003
Location
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

8. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
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. Registered User
Join Date
Dec 2003
Location
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

10. Registered 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.

11. Registered 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

#### Posting Permissions

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