# 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!  Register To Reply

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))  Register To Reply

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.`  Register To Reply

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.  Register To Reply

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  Register To Reply

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))  Register To Reply

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  Register To Reply

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!  Register To Reply

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.  Register To Reply

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

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

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