# SUMIF Date in Range is a specific month

1. ## SUMIF Date in Range is a specific month

This is a dynamic worksheet with new rows added daily throughout the year. I want to get monthly totals from the data so I can create a line chart on the monthly totals.

Column A is DATE
Column B is Amount

In cell E2 I want to SUMIF the month in the date range is 1
In cell F2 I want to SUMIF the month in the date range is 2
In cell G2...SUMIF month...is 3

and so on

I tried variations of =SUMIF(A:A,MONTH(A:A)="1",B:B) and =SUMIF(MONTH(A:A),"1",B:B)

Thank you!

2. ## Re: SUMIF Date in Range is a specific month

Try in E2 copied to the right >> =SUMPRODUCT(--(TEXT(\$A\$2:\$A\$12,"mmmm")=E1),\$B\$2:\$B\$12)

Since you said you will be adding row, then we can make this dynamic.

Create two named ranges
myAmount =OFFSET(Sheet1!\$B\$2,,,COUNT(Sheet1!\$B:\$B))
myDate =OFFSET(Sheet1!\$A\$2,,,COUNT(Sheet1!\$A:\$A))

Then in E2 copied to the right >> =SUMPRODUCT((TEXT(myDate,"mmmm")=E1)*(myAmount))

3. ## Re: SUMIF Date in Range is a specific month

Another way.

In E2 and filled across
Formula:
`Please Login or Register  to view this content.`

4. ## Re: SUMIF Date in Range is a specific month

jeffreybrown,

The first formula worked fine. Of course the range is limited to the first 12 rows.
The second formula is producing very large numbers, instead of 250 January equals 43,475. So something is off with it. I tried analyzing the formula and noticed the second formula didn't contain "--" in the first argument. Adding them changed the result to "0". Likewise, the first formula contains an array1 and array2, whereas the second formula has an asterisk "*" instead of a comma ",". Switching those out also produced a "0". I've uploaded the sample file for convenience.

5. ## Re: SUMIF Date in Range is a specific month

E2=if(e\$1<>"",sumproduct((month(\$a\$2:\$a\$100)=month(e\$1&0))*(\$a\$2:\$a\$100<>"")*(\$b\$2:\$b\$100)),"")

Copy across

6. ## Re: SUMIF Date in Range is a specific month

Hi ExSkidder,

Somehow the myDate and myAmount got turn around on the column references.

In the Name Manager, Swap A and B and it works fine.

myAmount =OFFSET(Sheet1!\$B\$2,,,COUNT(Sheet1!\$B:\$B))
myDate =OFFSET(Sheet1!\$A\$2,,,COUNT(Sheet1!\$A:\$A))

7. ## Re: SUMIF Date in Range is a specific month

In the first formula, I used what's call a double unary. It's just a method to coerce TRUE/FALSE to 1/0 so calculation can be made.

In the second formula I used the * which is just multiplying the two arrays.

To really investigate what is happening with a formula...

Evaluate Formula
• Click on the cell with the formula >> Ribbon >> Formulas >> Formula Auditing >> Evaluate Formula

8. ## Re: SUMIF Date in Range is a specific month

Originally Posted by jeffreybrown
In the first formula, I used what's call a double unary. It's just a method to coerce TRUE/FALSE to 1/0 so calculation can be made.

In the second formula I used the * which is just multiplying the two arrays.

To really investigate what is happening with a formula...

Evaluate Formula
• Click on the cell with the formula >> Ribbon >> Formulas >> Formula Auditing >> Evaluate Formula
Thank you for the additional instruction. It worked beautifully!

9. ## Re: SUMIF Date in Range is a specific month

Originally Posted by FlameRetired
Another way.

In E2 and filled across
Formula:
`Please Login or Register  to view this content.`
This was a great option also! Thank you FlameRetired.

10. ## Re: SUMIF Date in Range is a specific month

You are welcome. Glad to help. Thank you for the feedback and added rep.

There are currently 1 users browsing this thread. (0 members and 1 guests)