VBA to sum values in one column based on criteria of another column

[Jim885]

I need someone to help me with this, as I can't figure out how to do it.

The below sample is an accurate example of a much larger set of data that I need help with. Column H will be my desired result.
I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.

Thanks to everyone for looking at this.

	A	B	C	D	E	F	G	H
1	Item#		Description	Quantity		Month	Year
2	1001		Item 1
3
4	Date	Transaction Description
5	5/21/2013	TX		8.00		5	2013
6	5/24/2013	TX		16.00		5	2013
7	5/31/2013	TX		20.00		5	2013	44
8	6/1/2013	TX		1.00		6	2013
9	6/4/2013	TX		2.00		6	2013
10	6/5/2013	TX		8.00		6	2013
11	6/6/2013	TX		4.00		6	2013
12	6/11/2013	TX		4.00		6	2013
13	6/11/2013	TX		16.00		6	2013
14	6/14/2013	TX		2.00		6	2013
15	6/14/2013	TX		2.00		6	2013
16	6/14/2013	TX		2.00		6	2013
17	6/24/2013	TX		1.00		6	2013
18	6/24/2013	TX		4.00		6	2013
19	6/24/2013	TX		2.00		6	2013
20	6/25/2013	TX		2.00		6	2013	50
21	7/2/2013	TX		4.00		7	2013
22	7/2/2013	TX		1.00		7	2013
23	7/11/2013	TX		4.00		7	2013
24	7/11/2013	TX		6.00		7	2013
25	7/16/2013	TX		5.00		7	2013
26	7/18/2013	TX		1.00		7	2013	21
27	8/20/2013	TX		1.00		8	2013
28	8/23/2013	TX		1.00		8	2013
29	8/23/2013	TX		4.00		8	2013
30	8/26/2013	TX		2.00		8	2013
31	8/27/2013	TX		2.00		8	2013
32	8/30/2013	TX		4.00		8	2013	14

<colgroup><col style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;" width="36"> <col style="width: 48pt;" width="64"> <col style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;" width="29"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;" width="60"> <col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>

 

[Marcelo Branco]

Maybe this formula in H5 copied down

=IF(AND(F5=F6,G5=G6),"",SUMIFS($D$5:$D$1000,$F$5:$F$1000,F5,$G$5:$G$1000,G5))

M.

 

[Jim885]

Marcelo,
Thanks for this idea, and it does work. But I need a VBA code for this project. Could you provide that?

 

[Marcelo Branco]

Assuming your data in Sheet1 (adjust if needed) maybe...

Sub aTest()
    Dim lastRow As Long
    
    With Sheets("Sheet1") '<--Adjust sheet name
        lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        With .Range("H5:H" & lastRow)
            .Formula = "=IF(AND(F5=F6,G5=G6),"""",SUMIFS($D$5:$D$" & lastRow & _
                ",$F$5:$F$" & lastRow & " ,F5,$G$5:$G$" & lastRow & ",G5))"
            .Value = .Value
        End With
    End With
End Sub

Hope this helps

M.

 

[Jim885]

Marcelo,
Thanks for your effort. There is just one problem that I encounter now that I apply the macro to the whole file.

This file will encompass monthly sums for many item numbers for each month of the year. So, there will be a monthly value (1 ~ 12) in Column F for another item.

As the data runs down the sheet for each item, the monthly values in Column F repeat themselves for each item.

The formula that the macro inputs into each cell in Column H is summing every identical monthly value in that column down to the last row. That captures every monthly value below the subtotal. Thus it adds every instance of each month. Can the formula be adjusted avoid this situation?

 

[Marcelo Branco]

Sorry, I'm not following you.

Are you saying that there are rows with identical pairs month-year and that should be summed separately?

How the formula would distinguish between one set and another? Is there some field that we can use to this purpose?

M.

 

[Marcelo Branco]

Could you provide another data sample where such problem occurs?

M.

 

[Jim885]

Sorry, I'm not following you.

Are you saying that there are rows with identical pairs month-year and that should be summed separately?

How the formula would distinguish between one set and another? Is there some field that we can use to this purpose?

M.

Yes, there are other rows within the columns with identical pairs of months and years. That situation would be a different item number with usages for the same periods (months & year).
The distinguishing factor would be the item number (that is mixed with the dates in Column A), or item description found in Column C.
Another distinguishing factor would be the blocks that are separated by blank rows between each item??

The below sample would be a continuation of the above sample.

A	B	C	D	E	F	G	H
8/23/2013	TX		4.00		8	2013
8/26/2013	TX		2.00		8	2013
8/27/2013	TX		2.00		8	2013
8/30/2013	TX		4.00		8	2013	13
10102		Item 2
Date	Trans
4/22/2013	TX		5.00		4	2013	5
5/14/2013	TX		1.00		5	2013	1
6/1/2013	TX		1.00		6	2013
6/5/2013	TX		1.00		6	2013
6/10/2013	TX		1.00		6	2013
6/14/2013	TX		2.00		6	2013
6/24/2013	TX		1.00		6	2013	6.00
7/2/2013	TX		3.00		7	2013
7/16/2013	TX		5.00		7	2013
7/18/2013	TX		1.00		7	2013
7/19/2013	TX		1.00		7	2013	10.00
8/20/2013	TX		1.00		8	2013
8/22/2013	TX		2.00		8	2013
8/23/2013	TX		3.00		8	2013
8/26/2013	TX		2.00		8	2013
8/27/2013	TX		2.00		8	2013
8/29/2013	TX		1.00		8	2013	11.00
9/11/2013	TX		1.00		9	2013
9/11/2013	TX		2.00		9	2013
9/24/2013	TX		3.00		9	2013
9/24/2013	TX		3.00		9	2013	9.00

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" width="37"> <col style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" width="61"> <col style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;" width="60"> <col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>

 

[RadioME]

Hi,

I'm new to the forum (and very new to VBA) but I may have something that will help you as long as the data is in the same order as the one above.

Sub Fixed()

Dim lastrow As Integer
Dim Cumulative As Double
Dim i As Integer
Dim startrow As Integer




lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Cumulative = 0
startrow = 6 'Row where your data starts + 1, so row 5 + 1 in this case


For i = startrow To lastrow + 1
    Cumulative = Cumulative + Range("D" & i - 1).Value
    
    If Range("F" & i) <> Range("F" & i - 1) Then
        Range("F" & i - 1).Offset(, 2).Value = Cumulative
        Cumulative = 0
    End If
Next i


End Sub

 

[Jim885]

You're new to VBA? Are you kidding me? That code is great! It did exactly what I needed.
If you're new to VBA, you're going to be amazing pretty soon. But I think you're amazing already, I have been working on this code all day and got no where.
You must have some other kind of code background, because that code you provided is great.

Thanks very much!

You don't have to take on this next step I need, but while I have your attention, I thought I'd ask.

The next thing I need is to compile this data onto another worksheet where the Item numbers are placed in Column A, the descriptions go into Column B, then the totals for each month would be dropped into the respective columns to the right. The second sheet would have headers for each column. Like this;

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item #	Description	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13	Jan-14	Feb-14	Mar-14	Apr-14	May-14
2	10101	Item 1	44	50	21	14	45	7	19	30	31	36	42	82	16
3	10102	Item 2	1	6	10	11	9	6	5	2	3	16	14	10	4
4	10103	Item 3	2	7	1	2	0	1	0	2	0	6	5	1	0
5	10104	Item 4	0	2	1	3	0	2	0	0	0	4	4	2	0

<tbody>
</tbody>