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.
Australia-MastheadEE-example.xlsx
gregfthompsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this...

In S2
=SUMIF($P$2:$P$140,P2,$O$2:$O$140)

Open in new window

and copy it down.
Australia-MastheadEE-example.xlsx
0
Scott TownsendIT DirectorCommented:
Yes, the Formula you are looking for is:
in S2
=SUMIF($P$2:$P$140,P2,$O$2:$O$140)

Open in new window


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
Rob HensonFinance 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):

Masthead       Sum of Total P
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
Rob HensonFinance 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
Australia-MastheadEE-example.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gregfthompsonAuthor Commented:
Thanks - great help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.