Summing 2 or more rows after an IF statement

[sfiol]

OK, my first post and I'm going to let you know I'm not all that good with Excel. I can do some stuff, but it's not my forte.
I have a report at work that for whatever reason lists multiple rows for the same item number. What I need to do is compare the item numbers in row A. If they match, then I need to add (sum) column N - AF for the two (or more) matching rows.
My biggest problem that I see is that I don't know how to put the code in for a range of rows that compares each cell in column A to see if they match.
To make sure I give enough information - First, I need to compare the value in A4 to every other value in column A. If they match, I need to add the values in the matching row from column N to column AF. If they don't match, I need to move down to the next row in column A to see if that value matches any of the others, in a loop until finished. My problem is, I don't know what command (or commands) to use for a macro, and I don't have the skill to try to write the code for it. Can someone at least point me in the right direction for what I'm looking for?
Thanks in advance!

 

[Jerry Sullivan]

Hello and Welcome,

Two methods that might work are:
-Using a PivotTable
-Sorting your data by Column A and adding Subtotals

If you don't have any experience with these you could look them up in Excel's Help and post a question if you get stuck. It's not practical to try to describe either of those methods step by step in a post - however those are probably the best options to consider.

Good luck!

 

[sfiol]

Jerry,

Thanks for the reply. Unfortunately, I don't know what I'm doing. I've tried the pivot table, and can't get that to work (ignorance). I already sort the data by column A. Due to issues with the people at work, subtotals aren't a viable solution.
Right now, someone has to go through the entire sheet and manually add the rows together. I'm just looking for a better way to do that. I'll figure it out somehow. I'm pretty persistent.

Thanks again for the response.

 

[Jerry Sullivan]

What version of Excel are you using?

 

[sfiol]

Microsoft Office Excel 2007 (12.0.4518.1014) MSO (12.0.4518.1014)

When I try to make the pivot table, it was giving me an error. This time, it created it, but I have no idea how to go about creating the list I need.

 

[sfiol]

Now I'm back to the 'PivotTable Field Name Is Not Valid' error. I thought I was doing the exact same thing. Maybe I need to find someone who knows a lot more about Excel than I do!

 

[Jerry Sullivan]

Another option, using SUMIF, might be easier to explain...

If this is your current source data.....

<b>Source Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:120px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >N</td><td >O</td><td >P</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Customer</td><td >Header</td><td >Header</td><td >Header</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Account 123</td><td style="text-align:right; ">114</td><td style="text-align:right; ">891</td><td style="text-align:right; ">786</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Account 123</td><td style="text-align:right; ">929</td><td style="text-align:right; ">932</td><td style="text-align:right; ">158</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Account 456</td><td style="text-align:right; ">626</td><td style="text-align:right; ">853</td><td style="text-align:right; ">268</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Account 123</td><td style="text-align:right; ">815</td><td style="text-align:right; ">153</td><td style="text-align:right; ">287</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Account 456</td><td style="text-align:right; ">455</td><td style="text-align:right; ">571</td><td style="text-align:right; ">241</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Account 789</td><td style="text-align:right; ">419</td><td style="text-align:right; ">385</td><td style="text-align:right; ">849</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


1. Make a copy of the entire sheet and rename the sheet tab as Summary.
The rest of these steps are to be done in your new Summary sheet:
2. Clear all your data except Column A and the Headers
3. Use Remove Duplicates on Column A to get just the unique values in Col A.
4. Put this formula in Cell N2 (substitute your Source Data's sheet name):
=SUMIF('Source Data'!$A:$A,Summary!$A2,'Source Data'!N:N)
5. Copy that formula from N2 to the entire range of N2 to the last row of AF.

That should give you the sums in formulas. The end result will look like this...
<b>Summary</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:120px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >N</td><td >O</td><td >P</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Customer</td><td >Header</td><td >Header</td><td >Header</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Account 123</td><td style="text-align:right; ">1858</td><td style="text-align:right; ">1976</td><td style="text-align:right; ">1231</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Account 456</td><td style="text-align:right; ">1081</td><td style="text-align:right; ">1424</td><td style="text-align:right; ">509</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Account 789</td><td style="text-align:right; ">419</td><td style="text-align:right; ">385</td><td style="text-align:right; ">849</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >N2</td><td >=SUMIF('Source Data'!$A:$A,Summary!$A2,'Source Data'!N:N)</td></tr><tr><td >O2</td><td >=SUMIF('Source Data'!$A:$A,Summary!$A2,'Source Data'!O:O)</td></tr><tr><td >N3</td><td >=SUMIF('Source Data'!$A:$A,Summary!$A3,'Source Data'!N:N)</td></tr><tr><td >O3</td><td >=SUMIF('Source Data'!$A:$A,Summary!$A3,'Source Data'!O:O)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[sfiol]

OK, I made it through number 4. I have Sheet1 (with all of the original data in it) and Sheet2 which has been renamed Summary. I deleted all data except for all of column A and Row 3 (the headers). I removed duplicates on column A. I put the formula =SUMIF('Sheet1'!$A:$A,Summary!$A2,'Sheet1'!N:N) into cell N2. I'm not sure what you mean by 'entire range'.
I really do appreciate the help though!

 

[Jerry Sullivan]

First, check if Cell N2 has the correct sum for that Row.

If so you want to copy the formula from N2 to all the other cells in your Columns N:AF that need the formula (that's what I meant by range).

Because the formula in N2 uses a combination of references with and without dollar signs, the formula will adjust automatically in the cells so you don't have to go in and change "N" to "M" in all the cells in Col M.

Do you need help copying and pasting the formula?

 

[sfiol]

Makes sense now. I'll see if it works.