Can't make conditional lookups work with SUMIF.

[RoryTuna]

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&"*",Totals>1),Totals)),""
-and-
=SUMPRODUCT(--(Totals>1),--(Widgets=A1&"*"))

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

Thanks
RT

 

[T. Valko]

Try one of these:

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

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

Biff

 

[RoryTuna]

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]

You're welcome. Thanks for the feedback!

Biff

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