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

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

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))}

Thank you for those two suggestions. I went with the array formula. This is done in VBA using Selection.FormulaArray =.
Jerry

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))"

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.

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

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.

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

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

Jerry, those are some good reasons to use SumProduct and good examples. I will keep this in mind for future use.