Excel formulas (sum with multiple criteria)

Microsoft Microsoft excel 2007 full vers...
January 8, 2010 at 18:27:31
Specs: Windows 7, core duo2 4gig
hi i have just joined so i hope this is enough info

I have the following datatable headings:
date trans_categ debit credit difference

I want to sum the values in the difference field if date is in january AND if transaction category = "car insurance"
what formula do i need to use?

thanks for your help

See More: Excel formulas (sum with multiple criteria)

Report •

January 8, 2010 at 19:26:15
I think SUMPRODUCT will get you what you want.

If you are using Named Ranges, the formula might look like this (all on one line)

=SUMPRODUCT((MONTH(date)=1)*(trans_categ="car insurance")*(difference))

If you aren't using Named Ranges, then you should be. ;-)

Actually, if you aren't, just replace my Named Ranges (date, trans_categ and difference) with the range of cells that contain the values under those headings.

P.S. You did a fine job of giving us enough info to answer your question, but your Subject Line left a little to be desired.

If everyone used a Subject Line like "Excel Formula" we wouldn't be able to tell one thread from another.

I took the liberty of editing your Subject Line so that it gives the members a little bit of an idea of what the thread is about.

Report •

January 9, 2010 at 03:59:47
Thanks for such a quick reply, I spent days trying to work that out. I f wanted month and year do I type:

thanks again

Report •

January 9, 2010 at 06:32:53
If you want to check the Month and the Year you have to evaluate each criteria individually.

SUMPRODUCT uses a TRUE-FALSE array (evaluated as 1 or 0) for each criteria to determine which values to SUM.

If you want to see it in action, select the cell with the formula and click on Tools...Formula Auditing...Evaluate formula. Clicking the Evaluate button will allow you to "single step" through the formula so you can see the arrays as they are evaluated.

Where each criteria is TRUE; you get a 1, where it's FALSE you get a 0. Once all of the criteria arrays have been evaluated, the magic begins.

If all criteria for a given value are TRUE, you'll essentially have:

1*1*ValueX = ValueX.
1*1*ValueZ = ValueZ

If one or more of the criteria are FALSE, you'll have something like:

1*0*ValueY = 0.

After all the PRODUCTs have been evaluated, the results are SUMed, thus the name SUMPRODUCT.

ValueX + 0 + ValueZ = The sum of the values where all criteria are TRUE.

The new formula would look something like this (all on one line)

=SUMPRODUCT((MONTH(date)=1)*(YEAR(date)=2010)*(trans_categ="car insurance")*(difference))

Report •

Related Solutions

Ask Question