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?
 
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
 
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
 
gregfthompsonAuthor Commented:
Thanks - great help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.