Hi everyone,
I really need help with something that's driving me crazy.I am working on a spending report where I have a list of bank transactions on the 1st worksheet, and then subsequent worksheets are for each month of the year and other data on them.
I created this whole spending report at home on Excel 2007; however, at work where I need it I work on Excel 2003. I need help converting the below SUMIFS formula to a valid Excel 2003 version.
=SUMIFS('Master Spending Trans Summary'!C:C,'Master Spending Trans Summary'!A:A,'Jan. 2012'!A12,'Master Spending Trans Summary'!B:B, ">=" & "1/1/2012", 'Master Spending Trans Summary'!B:B, "<=" & "1/31/2012")
Whenever I've tried to convert it using SUMPRODUCT or SUM(IF, I keep getting either #NAME? or a #NUM! error.
Anyone who knows how to convert this formula, please please please help me!!
Hopefully someone can help convert this quickly, as I need it soon!
Thanks in advance!!
Try:
=SUMPRODUCT('Master Spending Trans Summary'!$C$2:$C$100,--('Master Spending Trans Summary'!$A$2:$A$100='Jan. 2012'!A12),--('Master Spending Trans Summary'!$B$2:$B$100>="1/1/2012"+0),--('Master Spending Trans Summary'!$B$2:$B$100<="1/31/2012"+0))
Note: Adjust ranges to suit. You can't use whole column references with this formula in 2003.. also, keep the ranges to a minimum, as Sumproduct can be resource intensive.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks