Excel formulas (sum with multiple criteria)

DerbyDad03 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.

Thanks for such a quick reply, I spent days trying to work that out. I f wanted month and year do I type: month&year(date)=1&2010 thanks again

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