# Sum numbers in Column O cells, where cells in column P have the same name, and repeat the total in column S.

Excel file where there are numbers in column O, corresponding with a name in column P.
In the example file, rows 2 to 15 have the same name in column P.
In the example file, rows 16 to 29 have a different name in column P.
The request is for a script to sum the contents of the cells in column O for the number of rows that have the same name in column P, and place the total in each corresponding cell in column S.
In the example, The first two names have been completed manually.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel & VBA ExpertCommented:

In S2
``````=SUMIF(\$P\$2:\$P\$140,P2,\$O\$2:\$O\$140)
``````
and copy it down.
0
IT DirectorCommented:
Yes, the Formula you are looking for is:
in S2
``````=SUMIF(\$P\$2:\$P\$140,P2,\$O\$2:\$O\$140)
``````

the \$ in the Cell names Fix the Cells so they don't change when copying or filling.
So when you fill down, the Middle 'P2' will increment
It will compare everything in \$P\$2:\$P\$140 to P2, then add the Value in O if it Matches.
0
Finance AnalystCommented:
As suggested above, the use of SUMIF does what you ask for. Do you really want the total repeated against each line?

There are a few other options for getting summation of a table of data.

1) Assuming data is sorted on column P, you could adjust the SUMIF formula so that it only occurs on the last row of each set:

=IF(P2=P3,"",SUMIF(P:P,P2,O:O))

2) Pivot Table - a Pivot table can show the same results but with just one row per masthead:

Result would look like (values in one column, unlike copy & paste below):

Whittlesea Leader - Digital Only       6,916
Whyalla News       3,600
Wingham Chronicle       6,973
Wyndham Star Weekly       12,490
Wynnum Herald       14,915
Yarrawonga Chronicle       2,326
Yass Tribune       3,827
Yorke Peninsula Country Times       4,198
Grand Total       55,245

3) Subtotal - again assuming sorted on masthead, you could run the Subtotal wizard which would insert a row below each group with a total for each and a grand total at the bottom. This also adds grouping to the data so that rows can be collapsed or expanded to show more or less data ie deatil or just subtotals.
0
Finance AnalystCommented:
See attached file with the three options described above.

Sheet
Local - with SUMIF but only on last line of each masthead
Pivot - with Pivot Table summary
Local (2) - with SUBTOTALs inserted after each change in Masthead
0

Experts Exchange Solution brought to you by