There *will* be duplicate last names, but I'm going to try your suggestion
anyway to see if I can understand how your formula works. I'll report back
tomorrow -- it's closing time here.

Thanks much!

"RagDyer" wrote:

If you assume that there are *no duplicate* last names, use TTC (Text To
Columns) to create a "helper" column, and have your formula reference that
helper column to perform your calculations.

With data in say A2 to Bn, select A2 to An, and then, from the Menu Bar,
<Data <Text To Columns
Click on "Delimited". then <Next,

Then click on "Space", then <Next,

In the "Data Preview" window, the left most column is selected by default.
In the "Destination" box, enter
D2

Then click in the next column (second from left) to select it,
And click on "Do Not Import".
You'll see the column header in the Preview window change to "Skip".
Do the same to any other columns remaining, leaving only the first, left
column header displaying "General".

Then click <Finish

You should now have all your original data, with your "helper" Column D
displaying only the last names from Column A.

Now, in C2, enter this formula:

=IF(SUMPRODUCT(($D$2:$D$50=D2)*$B$2:$B$50)1600,SU MPRODUCT(($D$2:$D$50=D2)*$B$2:$B$50),"")

And copy down as needed.

I've sized this formula for only 50 rows.
Adjust the ranges to suit.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"t_perkins" wrote in message
...
Still hunting for a solution to a problem posted earlier. I have a data
table that contains a list of donations, with the name of the donor in
column
A and the amount of each donation in column B. Some donors gave more than
once, and I need to be able to calculate their aggregate donations. In
the
end, I'm only interested in displaying totals for donors who gave more
than
$1,600 -- whether through a single donations, or multiple donations. I
want
to display the totals in column C.

What formula will allow me to calculate totals for all donors, including
names that are similar but not exact matches (e.g., grouping PERKINS JOHN
W
together with PERKINS JOHN), and then show totals *only* for donors whose
total donations exceed $1,600?

Here's some sample data -- in my actual data table, there are thousands of
rows, and donors have given anywhere from one to ten or more times. As
you
can see, the names aren't always entered the same way. My early research
indicates that I should maybe use the LEFT function to compare the
beginning
characters of names, but please tell me if there's another way.

A B C
Contributor Name Amount Over 1,600?
ABBOTT DOROTHY C 1500
ABBOTT DOROTHY C 15
ANDREWS KATE 100
ANDREWS KATE J 50
BROWN MICHAEL 25
DALTON KUMI 1400
FRENCH KATE K 1550
FRENCH KATHERINE K 100
GORDON NANCY 10
LINCOLN EDDIE 25
LINCOLN ELLEN 25
LINCOLN ELLEN M 1000
LINCOLN ELLEN M 800
LINCOLN ELLEN M 50

Mr. t

It looks to me like you need another column that you can use for
grouping purposes. Even with thousands of rows you can probably
populate an additional column with a unique name (or donor number)
much more easily than you can come up with a formula that can
distinguish between all the names and versions of names that people
can come up with.

If you started by using advance filtering to filter a list of unique
names to another location, then put the name (or number) you would
want to associate with all versions of that donor's name, then put a
new column in your data base and populated it with a VLOOKUP formula
to pick up the desired name (or number), I think you would find that
you could very easily use SUM, SUMIF, SUMPRODUCT, or a Pivot Table or
someother basic tool of your liking to generate the summary that you
want. You may find that you can clean up your name list with a few
search/replaces, TRIMs, maybe text to columns or some other tools.

People in this group do come up with some really good formulas, so
maybe you will get one that will work for you; but, I think fixing the
source a problem is often better.

Good luck.

Ken
Norfolk, Va

On May 6, 8:04*pm, t_perkins
wrote:
There *will* be duplicate last names, but I'm going to try your suggestion
anyway to see if I can understand how your formula works. *I'll report back
tomorrow -- it's closing time here.

Thanks much!



"RagDyer" wrote:
If you assume that there are *no duplicate* last names, use TTC (Text To
Columns) to create a "helper" column, and have your formula reference that
helper column to perform your calculations.

With data in say A2 to Bn, select A2 to An, and then, from the Menu Bar,
<Data <Text To Columns
Click on "Delimited". then <Next,

Then click on "Space", then <Next,

In the "Data Preview" window, the left most column is selected by default.