hi i have just joined so i hope this is enough info I have the following datatable headings:
date trans_categ debit credit differenceI 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
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&2010thanks again
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 = ValueZIf 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))