SUMIF Date in Range is a specific month

[ExSkidder]

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!

[jeffreybrown]

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))

[FlameRetired]

Another way.

In E2 and filled across

Please Login or Register  to view this content.

[ExSkidder]

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.

[CARACALLA]

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


Copy across

[jeffreybrown]

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))

[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

[ExSkidder]

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!

[ExSkidder]

Another way.

In E2 and filled across

Please Login or Register  to view this content.

This was a great option also! Thank you FlameRetired.

[FlameRetired]

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

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.