Is there a function that can ...?

[Guest]

Good Day.
I hope someone can help me with this little problem.

Source data is a list of Names (col A) with corresponding numerical values
(cols B to F). Each Name can have multiple, varying occurrences within this
list.

I wish to summarise, in another worksheet, the sum for each individual
person. I am using sumif to sum each person’s values, which is working fine.
The problem is how to have the summary table automatically find and list the
names from the source, with the name occurring only once, with the numerical
value being the sum of all the values for that person’s multiple occurrences
(sumif as mentioned above). Also if a new name appears in the source, it
automatically appears in the summary listing, with its applicable summary.
Pivot table did not work. Which function would be best to use?

Many thanks in advance.
DeanH

 

[Bill Ridgeway]

Have you tried a Pivot Table?

Click <Data><PivotTable and PivotChart Report> and follow the prompts.

Regards.

Bill Ridgeway
Computer Solutions

 

[Guest]

Thanks Bill for the swift response (Merry Christmas to you and yours).

I have tried a Pivot table (I must admit I am not the best user of Pivots)
but do not get the results I want.
The Pivot table wants to place the sum returns in rows alongside each
person. I want the sums to appear in the columns alongside each person. ie
they are at present cols B to F. I want them to appear as columns in the
pivot table not rows. So each person is a single row, not each person having
5 rows to their name.

I cannot get the Pivot to return the columns as columns (sorry dont know how
better to expain this), do you know of a way?

Or is there a function that can extract each name from a list of names, once
only?

Thanks
DeanH

 

[Bill Ridgeway]

Dean,

Pivot Tables can be configured to a varying range of presentation and it
sounds as though you have a reasonable expectation. They're not as
intuitive as you may expect. When the Pivot Table has been 'built' it can
be changed from the 'default' presentation by dragging and dropping the
'headings' (the grey box with a down arrow) until you get what you want.
I'm afraid it's a choice of RTFM (Read the flipping manual) or 'suck it and
see'.

Regards.

Bill Ridgeway
Computer Solutions

 

[Bob Phillips]

Dean,

This should get the list of names, then just use SUMIF as normal

On Sheet2!A1 put =Sheet1!A1

A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1:$A$20),MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$20&""),0)))

which is an array formula, commit it with Ctrl-Shift-Enter, not just Enter.

Copy A2 down.



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Ron Rosenfeld]

Thanks Bill for the swift response (Merry Christmas to you and yours).

I have tried a Pivot table (I must admit I am not the best user of Pivots)
but do not get the results I want.
The Pivot table wants to place the sum returns in rows alongside each
person. I want the sums to appear in the columns alongside each person. ie
they are at present cols B to F. I want them to appear as columns in the
pivot table not rows. So each person is a single row, not each person having
5 rows to their name.

I cannot get the Pivot to return the columns as columns (sorry dont know how
better to expain this), do you know of a way?

Or is there a function that can extract each name from a list of names, once
only?

Thanks
DeanH

You'll need to set up a new, calculated field in the Pivot Table that is the
sum of the five values. Then drag the Names to the row area, and this new
field to the data area.
--ron

 

[Bill Ridgeway]

As far as I know it isn't possible to format cells in a Pivot Table. The
order can be sorted (in the usual way). The order can also be changed by
typing data into a cell (useful if, for instance you want to re-order a list
of PostCodes) but be careful not to type something that doesn't exist.

Information in cells will revert to the 'standard' format when refreshed.
It is, however, possible to copy the data to another area and then do what
you like with it - as long as the basic structure of the Pivot Table isn't
changed. This can be done either by formula or <Copy><Paste
Special><Values>.

Regards.

Bill Ridgeway
Computer Solutions

 

[KC Rippstein]

Dean,
I have read through these responses and think I can get you an answer to
your original request.
Have the pivot table run off of the entire column A. Let's assume the pivot
table is generated on Sheet3 and the first unique name shows up in cell A5.
Turn off the totals rows.
On your "summary" worksheet:
A2 =IF(isblank(Sheet3!A5),"",Sheet3!A5)
B2 =SUMIF(Sheet1!A:A,$A2,B:B)
Use B2 fill handle to drag across to F2
Select A2:F2 and use fill handle to drag down however far you like (let's
say 100 rows).
In A101, =COUNTA(A2:A101), B101 =SUBTOTAL(9,B2:B101), and so on.
Select A1:F101 and apply autofilter, then under the column A filter choose
"NonBlanks" and always leave it engaged.

That should get you back to your original request, assuming the pivot table
auto-updates (which I don't know the answer to that...I think it
auto-updates every time you open the workbook, maybe?).

- KC

 

[KC Rippstein]

Sorry, that B2 =SUMIF(Sheet1!A:A,$A2,B:B)
should have been B2 =SUMIF(Sheet1!$A:$A,$A2,Sheet1!B:B)