How to use SUMIFS without crying
September 30, 2010 10:45 PM Subscribe
Can I use Excel 2007's SUMIFS function to do the following on a budget/finance report worksheet:
Sum all of the transactions given a specific category AND a specific month.
i.e. Add up all transactions categorised as Entertainment in August.
I currently have a sumifs function that says:
=SUMIFS(ExpAmount,ExpMonths,8,ExpECCRef,1)
Where ExpAmount is a range that has the transaction amounts, ExpMonths is a range that returns a Month (1-12) and ExpECCRef is a range that correlates to categories (1 in this case being 'Entertainment')
I also tried
=SUMIFS(ExpAmount,ExpMonths,8,ExpCat,"Entertainment")
But thought this wasn't working because '8' and "Entertainment" are different data types, which led to ExpECCRef (which still didn't fix the problem)
Both of these return #VALUE!
Once this is all cleaned up I will use it to work with variable categories and months using VLOOKUPs (which I can handle fine), but I need to get the SUMIFS working first.
I've never used SUMIFS before though so I am struggling and I think I may be trying to do something that simply cannot be done with SUMIFS...in which case: How can I achieve what I am trying to do?
i.e. Add up all transactions categorised as Entertainment in August.
I currently have a sumifs function that says:
=SUMIFS(ExpAmount,ExpMonths,8,ExpECCRef,1)
Where ExpAmount is a range that has the transaction amounts, ExpMonths is a range that returns a Month (1-12) and ExpECCRef is a range that correlates to categories (1 in this case being 'Entertainment')
I also tried
=SUMIFS(ExpAmount,ExpMonths,8,ExpCat,"Entertainment")
But thought this wasn't working because '8' and "Entertainment" are different data types, which led to ExpECCRef (which still didn't fix the problem)
Both of these return #VALUE!
Once this is all cleaned up I will use it to work with variable categories and months using VLOOKUPs (which I can handle fine), but I need to get the SUMIFS working first.
I've never used SUMIFS before though so I am struggling and I think I may be trying to do something that simply cannot be done with SUMIFS...in which case: How can I achieve what I am trying to do?
Response by poster: Ok, it works for me too - the problem was caused by a lazy workaround and blank cells.
Each of the named ranges contain blank cells because I will be adding transactions to this sheet over time. I don't want to redefine the names each time I add a transaction, so is there a way to have the range expand to fit each new transaction as it is added?
Thanks for the help so far!
posted by man down under at 12:38 AM on October 1, 2010
Each of the named ranges contain blank cells because I will be adding transactions to this sheet over time. I don't want to redefine the names each time I add a transaction, so is there a way to have the range expand to fit each new transaction as it is added?
Thanks for the help so far!
posted by man down under at 12:38 AM on October 1, 2010
Response by poster: I think I may have solved it by creating a Dynamic Named Range using the Offset function.
Excel is beautiful and somewhat frustrating at times!
posted by man down under at 1:06 AM on October 1, 2010
Excel is beautiful and somewhat frustrating at times!
posted by man down under at 1:06 AM on October 1, 2010
This thread is closed to new comments.
posted by prenominal at 11:36 PM on September 30, 2010