Sign in with
Sign up | Sign in
Your question

Excel formula problem: Sumifs

Tags:
  • Sumifs
  • Apps
  • sumif
  • Formula
  • Microsoft
  • Excel Formula
August 13, 2014 12:17:01 AM

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?

More about : excel formula problem sumifs

!