You're welcome. Thanks for the feedback!

Biff

"RoryTuna" wrote in message
...
Hi Biff,

This works! I was really amazed to finally see the numbers I expected,
thanks!
I was even able to subsitute the range names and a cell reference instead
of
the name in qutes (for flexibility) and I am getting the expected results.

Thanks again! I can now try to reclaim my sanity... :-)

RT

"T. Valko" wrote:

Try one of these:

=SUMPRODUCT(--(ISNUMBER(SEARCH("CompA",A1:A6))),--(B1:B61),B1:B6)

=SUMPRODUCT(--(LEFT(A1:A6,5)="CompA"),--(B1:B61),B1:B6)

Biff

"RoryTuna" wrote in message
...
I need to look into a range of cells within a Pivot table that contains
Text
strings and values. the text strings are a single cell that is a
contatenation of company names, country codes, and numeric text. The
numeric
values in the adjacent cells are a count of how many times the
particular
company/country code/number appear. I need to sum all the numeric
values
associated with each company, but only when those values are greater
than
1.

Sample data:

Widgets Totals

CompA US 1234 2
CompA EU 3456 2
CompA JA 1897 1
CompB AR 7890 3
CompC US 7654 4
CompD EU 2323 1

My expected results should be:

CompA 4
CompB 3
CompC 4

I use a seperate list of unique company names that gets generated along
with
the Pivot table. I can use it to succesfully use a COUNTIF and find all
unique entries in the pivot table with COUNTIF(Widgets,(A1&"*")), where
A1
is
the cell where the name CompA is stored. But when trying to use a SUMIF
to
index into the company names that only have values greater than 1, I
can't
seem to use wildcards. I've also tried DSUM, SUMPRODUCT, but results
are
elluding me.

Here's a few samples I've tried, but either get a 0 result or #NAME
error
or
other joyless responces:

=SUMIF(Widgets:Totals,AND(Widgets=A1&"*",Totals1) ,Totals)),""
-and-
=SUMPRODUCT(--(Totals1),--(Widgets=A1&"*"))

I've been at this for days now and would greatly appreciate being set
right!

Thanks
RT