Combinng functions to lookup data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DP - 19 May 2008 16:06 GMT

I have a list of names (most repaeted several times) in column A that act as
a diary of work completed. I need excel to look at the names in column A and
then count / sum information found in another column. I have tried Vlookups,
if and countifs but to no avail. Any help would be appreciated.

DP

Reply to this Message

Pete_UK - 19 May 2008 16:24 GMT

You may need Sumproduct, along the lines of:

=SUMPRODUCT((A1:A100="name")*(D1:D100))

Sumif should also work:

=SUMIF(A1:A100,"name",D1:D100)

and Countif if you want to count them:

=COUNTIF(A1:A100,"name")

I've assumed that the values you want to sum are in column D, and that
you have 100 rows of data - adjust to suit.

Hope this helps.

Pete

> I have a list of names (most repaeted several times) in column A that act as
> a diary of work completed. I need excel to look at the names in column A and
> then count / sum information found in another column. I have tried Vlookups,
> if and countifs but to no avail. Any help would be appreciated.
>
> DP

Reply to this Message

Max - 19 May 2008 16:28 GMT

Assume the 2 col headers are: "Name" & "XXX"
Build a pivot table, with "Name" placed in ROW area, & "XXX" placed twice in
DATA area (one as COUNT, the other as SUM). Then go to the pivot, drag DATA &
drop it over "Total", and you'd get the desired results, viz.: a uniques
listing of the names in col A, with corresponding counts & sums (from col
"XXX") in adjacent cols B and C

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I have a list of names (most repeated several times) in column A that act as
> a diary of work completed. I need excel to look at the names in column A and
> then count / sum information found in another column. I have tried Vlookups,
> if and countifs but to no avail. Any help would be appreciated.
>
> DP

Reply to this Message