SUMIFS and dynamic date ranges

[apgdcg]

Hello,

I am having a hard applying what I've learned about SUMIFS to my data. If column A is mm/dd/yy, and column B is the data I want to sum, can I sum by month, for all the years in my data set? I'd like to be able to have a formula that is dynamic enough to be copied down every row.

Thanks all!

 

[Joe4]

Welcome to the Board!

I'd like to be able to have a formula that is dynamic enough to be copied down every row.

I assume you mean for each of the twelve months (i.e. the first row is January, second is February, ..., twelfth is December).

Not sure why you would use SUMIFS if you only have one criteria (SUMIFS is for more than one criteria, SUMIF is for one criteria). Regardless, I would probably use SUMPRODUCT.
Let's say that your dates are in cells A2:A100 and the values you want to sum are in cells B2:B100.
Now let's say that you want the January totals in cell D2, February in D3, etc.
Then, in cell D2, enter this formula:

=SUMPRODUCT(--(MONTH(A$2:A$100)=ROW()-1),--(B$2:B$100))

and copy down a total of 12 rows (one for each month).

If you are placing the first formula on any row besides row 2, you will need to make a minor adjustment, specifically, you will need to change this part:
=ROW()-1
You will need to change the 1 to the number that is one less than the row number you are putting the formula on.
So if you were placing the total on row 102, you would use:
=ROW()-101

 

[apgdcg]

Thank you so much, Joe4!

Here is what I mean:
Right now I have ~1100 rows of data, and I anticipate adding more.

Column A Column B
1/1/1999 .00
1/2/1999 .00
1/3/1999 .04
.
.
.
12/31/2001 .08

I would like a sum of (column B) Jan 99, Feb 99, Mar 99 ... Dec 2001.

Is there a way to make my sum_range dynamic, and/or do I somehow create 36 criteria, and 36 criteria ranges?

Thanks again,
A

 

[markmzz]

Maybe this:

Layout

[TABLE="width: 153"]
<tbody>[TR]
[TD="width: 63, bgcolor: transparent"]Date[/TD]
[TD="width: 34, bgcolor: transparent"]Value[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 42, bgcolor: transparent"]Month[/TD]
[TD="width: 47, bgcolor: transparent"]Total[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]jan/99[/TD]
[TD="bgcolor: yellow, align: right"]138[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]02/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]fev/99[/TD]
[TD="bgcolor: yellow, align: right"]139[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]03/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]mar/99[/TD]
[TD="bgcolor: yellow, align: right"]171[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]04/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]abr/99[/TD]
[TD="bgcolor: yellow, align: right"]116[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]05/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]mai/99[/TD]
[TD="bgcolor: yellow, align: right"]130[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]06/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]jun/99[/TD]
[TD="bgcolor: yellow, align: right"]130[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]07/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]jul/99[/TD]
[TD="bgcolor: yellow, align: right"]127[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]08/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]ago/99[/TD]
[TD="bgcolor: yellow, align: right"]129[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]09/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]set/99[/TD]
[TD="bgcolor: yellow, align: right"]117[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]out/99[/TD]
[TD="bgcolor: yellow, align: right"]146[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]nov/99[/TD]
[TD="bgcolor: yellow, align: right"]143[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]dez/99[/TD]
[TD="bgcolor: yellow, align: right"]148[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]jan/00[/TD]
[TD="bgcolor: yellow, align: right"]153[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]fev/00[/TD]
[TD="bgcolor: yellow, align: right"]131[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]15/01/1999[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]********[/TD]
[/TR]
</tbody>[/TABLE]

Create the dynamics names

MyDate - Refers To: =Sheet1!$A$2:ÍNDICE(Sheet1!$A:$A;CORRESP(9^9;Sheet1!$A:$A))

MyValue - Refers To: =Sheet1!$B$2:ÍNDICE(Sheet1!$B:$B;CORRESP(9^9;Sheet1!$A:$A))

Formula

In E2

=SUMPRODUCT(--(MyDate-DAY(MyDate)+1=$D2),MyValue)

And copy down.

Markmzz

 

[Joe4]

Quote from PM:

Each row is a day, not month. I want to total a month's worth of column B. Isn't there a way to copy/paste the same formula into each end of month? Perhaps Offset can dynamically determined the correct range to total?

I am afraid that if I need some sort of helper table, I will be created more work for myself than if I just manually summed up each month.

Don't be confused by the formula having the MONTH function in it, it is running against a date. I didn't realize that you also wanted to include the year, and not just the month.

I don't think I understand what you are after. You posted a small sample of your data in your 2nd post, can you post your expected result from that sample?
Are you trying to do a running monthly sum as you go down the page?
Or just one total per month?
Where are these totals being placed?

Please provide as much detail as possible! The more you tell us, the more likely the answer we give you will suit your needs.

 

[apgdcg]

Joe4,

OK, here is more detail:

Date Stuff to count Sum of counted stuff
1/1/1999 .01
1/2/1999 .01
1/3/1999 .00
1/4/1999 .03
1/5/1999 .00
1/6/1999 .00
1/7/1999 .00
1/8/1999 .06
1/9/1999 .01
1/10/1999 .01
1/11/1999 .01
1/12/1999 .00
1/13/1999 .03
1/14/1999 .00
1/15/1999 .00
1/16/1999 .00
1/17/1999 .06
1/18/1999 .01
1/19/1999 .01
1/20/1999 .01
1/21/1999 .00
1/22/1999 .03
1/23/1999 .00
1/24/1999 .00
1/25/1999 .00
1/26/1999 .06
1/27/1999 .01
1/28/1999 .01
1/29/1999 .01
1/30/1999 .00
1/31/1999 .03 .41
2/1/1999 .00
2/2/1999 .00
2/3/1999 .00
2/4/1999 .06
2/5/1999 .01
2/6/1999 .01
2/7/1999 .01
2/8/1999 .00
2/9/1999 .03
2/10/1999 .00
2/11/1999 .00
2/12/1999 .00
2/13/1999 .06
2/14/1999 .01
2/15/1999 .01
2/16/1999 .01
2/17/1999 .00
2/18/1999 .03
2/19/1999 .00
2/20/1999 .00
2/21/1999 .00
2/22/1999 .06
2/23/1999 .01
2/24/1999 .01
2/25/1999 .01
2/26/1999 .00
2/27/1999 .03
2/28/1999 .00 .36
3/1/1999 .00
3/2/1999 .00


I would like one sum for every month, of every year. These columns are tables, so that should help keep a formula dynamic?
These monthly sums are going to be plotted in a scatter graph and regressed to test their statistical significance.

Thanks again!
Alyssa
3/3/1999 .06
3/4/1999 .01
3/5/1999 .01
3/6/1999 .01
3/7/1999 .00
3/8/1999 .03
3/9/1999 .00
3/10/1999 .00
3/11/1999 .00
3/12/1999 .06
3/13/1999 .01
3/14/1999 .01
3/15/1999 .00
3/16/1999 .03
3/17/1999 .00
3/18/1999 .00
3/19/1999 .00
3/20/1999 .06
3/21/1999 .01
3/22/1999 .01
3/23/1999 .01
3/24/1999 .00
3/25/1999 .03
3/26/1999 .00
3/27/1999 .00
3/28/1999 .00
3/29/1999 .06
3/30/1999 .01
3/31/1999 .01 .43
4/1/1999 .01
4/2/1999 .00
4/3/1999 .03
4/4/1999 .00
4/5/1999 .00
4/6/1999 .00
4/7/1999 .06
4/8/1999 .01

 

[Joe4]

Thank you. Sometimes a pictures says a thousand words. I see exactly what you are after now.
Let's say that your data is on rows 2 - 100, and you want the sums to go in column C.
Place this formula in cell C2 and copy down to C100, and it will give you what you have shown in your example:

=IF(OR(MONTH(A2)<>MONTH(A3),A3=""),SUMPRODUCT(--(MONTH(A$2:A$100)=MONTH(A2)),--(YEAR(A$2:A$100)=YEAR(A2)),--(B$2:B$100)),"")

 

[farmerscott]

Apgdcg,

have you tried a pivot table? It has some features of aggregating individual dates up to months and years.

cheers

FarmerScott

Hello,

I am having a hard applying what I've learned about SUMIFS to my data. If column A is mm/dd/yy, and column B is the data I want to sum, can I sum by month, for all the years in my data set? I'd like to be able to have a formula that is dynamic enough to be copied down every row.

Thanks all!

 

[markmzz]

Try this too:

In C2

=IF(OR(A3>EOMONTH(A2,0),A3=""),SUMPRODUCT(--(A$2:A2-DAY(A$2:A2)=A2-DAY(A2)),B$2:B2),"")

And copy down.

Markmzz

 

[apgdcg]

Joe4,

I took the liberty of "explaining" to myself the purpose of each section of this formula. Do you think this is a correct assessment?

Since the cell references are not absolute, it is "copyable", this is good. However, it doesn't work for my data I replaced the array references with named arrays, and the logical test works, but the whole formula together gives me "#value!"

What am I still doing wrong?


[TABLE="width: 960"]
<colgroup><col span="15"></colgroup><tbody>[TR]
[TD="colspan: 4, align: left"]↓logical_test[/TD]
[TD="colspan: 9, align: left"]↓ Value_if_true[/TD]
[TD="colspan: 2, align: left"]↓Value_if_false[/TD]
[/TR]
[TR]
[TD="colspan: 4"]IF(OR(MONTH(A2)<>MONTH(A3),A3=""),[/TD]
[TD="colspan: 9, align: left"]SUMPRODUCT(--(MONTH(A$2:A$100)=MONTH(A2)),--(YEAR(A$2:A$100)=YEAR(A2)),--(B$2:B$100)),[/TD]
[TD="colspan: 2, align: left"]"")[/TD]
[/TR]
[TR]
[TD="colspan: 4, align: left"]Logical test - Find the end of the month[/TD]
[TD="colspan: 7, align: left"]Concatenate Month and year, and Sum Column B, for the cell you are in[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2, align: left"]Put a blank in this cell, because you are not at the end of the month[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks for your patience :\
Alyssa