Software: Microsoft Excel
I would like to use a formula that can be dragged across the sheet. My table has several categories going down the column, and the month across the column. My problem is creating a formula that is able to sum the items that are within the month, and corresponds to the category on the side. Here's what I have:
Table that I need to fill in the formula
----------A---------------B----------C--------D-------E---
1----------------------Jan-14 | Feb-14 | Mar-14| Apr-14|
2---Apples ------|
3---Orange----- |------insert formulas here-----------
4---Pineapple---|
5---Strawberry--|
[The month is in date form so the formula is easier to do. (I wanted the formulas easy to change from year to year)]
Table that I am summing the information
----------A-------------------B---------------C--------
7---Date--------------Category------$ Amount----
8---Jan 2, 2014------Apples-----------$10-----
9---Feb 5, 2014----Pineapple---------$5-----
Let's say that I'm writing the formula for B2:
Sum range: $C$8:$C$9
Category Criteria range:$B$8:$B$9
Date criteria range: $A$8:$A$9
This formula works when I sum by category:
=SUMIF(Category Criteria range,Criteria,Sum range)
=SUMIF($B$8:$B$9,$A2,$C$8:$C$9)
This formula works when I sum by Date:
=SUMIFS(Sum range, date criteria range, date criteria1, date criteria range, date criteria2)
=SUMIFS(Sum range, date criteria range, >= Jan 2014, date criteria range, < Feb 2014)
=SUMIFS($C$8:$C$9,$A$8:$A$9,">="&B$1,$A$8:$A$9,"<"&B$2)
However, when I combine them like this, it doesn't work. The error is #VALUE!:
=SUMIFS(Sum range, date criteria range, date criteria1, date criteria range, date criteria2,category criteria range, category criteria)
=SUMIFS(Sum range, date criteria range, >= Jan 2014, date criteria range, < Feb 2014,$B$8:$B$9,$A2)
=SUMIFS($C$8:$C$9,$A$8:$A$9,">="&B$1,$A$8:$A$9,"<"&B$2,$B$8:$B$9,$A2)
I have also tried the following formulas, but they also don't work:
=SUMIFS($C$8:$C$9,$A$8:$A$9,">="&B$1,$A$8:$A$9,"<"&B$2,$B$8:$B$9,"="&$A2)
=SUMIFS($C$8:$C$9,$A$8:$A$9,">="&B$1,$A$8:$A$9,"<"&B$2,$B$8:$B$9,=$A2)
=SUMIFS($C$8:$C$9,$A$8:$A$9,">="&B$1,$A$8:$A$9,"<"&B$2,$B$8:$B$9,"="$A2)
Is there a way to do this without using pivot tables, and just formulas?