Can't make conditional lookups work with SUMIF.
 

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

Can't make conditional lookups work with SUMIF.
 

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

Can't make conditional lookups work with SUMIF.
 

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

Can't make conditional lookups work with SUMIF.
 

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