Sumproduct - use of some characters

[MrRJ]

Hello all,

In using Sumproduct, I have a column that contains similar products in which
I need to compute all of. For example, in the Product column, there are
mulitple 16.9oz products. Can I use 16.9*? I tried, and it didn't work. Is
left another option, if so, how would I use it?

Rich

 

[Mike H]

If i've understood correctly, try this

=SUMPRODUCT(--(LEFT(A1:A10,4)="16.9"))

Mike

 

[Rick Rothstein]

If the beginning of your entry is a number, you can get that number by using
this...

LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))

So in your 16.9oz products example, the above would return the 16.9 part of
it.

 

[T. Valko]

What does compute mean? Count?

Try something like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("16.9oz",A1:A100))))

Or:

=SUMPRODUCT(--(LEFT(A1:A100,6)="16.9oz"))

 

[Pete_UK]

You could use a term like this:

(LEFT(A1:A100,4)="16.9")

as one of the conditions in the SP formula, assuming it is column A
where your products are.

Hope this helps.

 

[MrRJ]

This is what I have.

=SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$O$2:$O$11818))-SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$M$2:$M$11818))*SUMIF('[WAP''s.xls]WAPs'!$A$2:$A$45,A271,'[WAP''s.xls]WAPs'!$B$2:$B$45)

Are you saying that I need to replace all the A column to "16.9"? I was
hoping to change the value in my A column like 16.9* meaning all values with
the 16.9 in front. Does that make sense?

Rich

 

[MrRJ]

Thanks Pete,
Question. Can I change the column A instead of typing in the 16.9 in the
formula?

Rich

 

[MrRJ]

Mike,
Thanks. However, is there a way that instead of using "16.9", I like to use
the SP to link to the cell that I want to find only the leftmost 4
characters? Does that make sense?

 

[T. Valko]

Well, the first thing *I* would do is rename that file to something much,
much, much shorter!

You can't directly use wildcards in SUMPRODUCT.

In your formula which range contains the 16.9oz?

--
Biff
Microsoft Excel MVP

This is what I have.

=SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$O$2:$O$11818))-SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$M$2:$M$11818))*SUMIF('[WAP''s.xls]WAPs'!$A$2:$A$45,A271,'[WAP''s.xls]WAPs'!$B$2:$B$45)

Are you saying that I need to replace all the A column to "16.9"? I was
hoping to change the value in my A column like 16.9* meaning all values
with
the 16.9 in front. Does that make sense?

Rich

What does compute mean? Count?

Try something like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("16.9oz",A1:A100))))

Or:

=SUMPRODUCT(--(LEFT(A1:A100,6)="16.9oz"))

 

[MrRJ]

I agree with you about the file names. I did not create them. I am
consulting for this company.

The CCDS Audit Transaction report is the one that is the source data for all
the various 16.9 products. I am using completely another file in which I
like to use a cell with the 16.9* to link to. Is that possible? Otherwise,
I would have to split out various product types. I like to group them.

Make sense??

Well, the first thing *I* would do is rename that file to something much,
much, much shorter!

You can't directly use wildcards in SUMPRODUCT.

In your formula which range contains the 16.9oz?

--
Biff
Microsoft Excel MVP

This is what I have.

=SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$O$2:$O$11818))-SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$M$2:$M$11818))*SUMIF('[WAP''s.xls]WAPs'!$A$2:$A$45,A271,'[WAP''s.xls]WAPs'!$B$2:$B$45)

Are you saying that I need to replace all the A column to "16.9"? I was
hoping to change the value in my A column like 16.9* meaning all values
with
the 16.9 in front. Does that make sense?

Rich

What does compute mean? Count?

Try something like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("16.9oz",A1:A100))))

Or:

=SUMPRODUCT(--(LEFT(A1:A100,6)="16.9oz"))

--
Biff
Microsoft Excel MVP


Hello all,

In using Sumproduct, I have a column that contains similar products in
which
I need to compute all of. For example, in the Product column, there
are
mulitple 16.9oz products. Can I use 16.9*? I tried, and it didn't
work.
Is
left another option, if so, how would I use it?

Rich

 

[T. Valko]

I like to use a cell with the 16.9* to link to.
If A1 = 16.9 ...

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,Sheet!A1:A100))))

However, that will match:

16.95lbs
16.9oz
16.9g
16.90
16.99

Anything that contains 16.9

So, if you're looking specifically for 16.9oz enter 16.9oz in A1.

--
Biff
Microsoft Excel MVP

I agree with you about the file names. I did not create them. I am
consulting for this company.

The CCDS Audit Transaction report is the one that is the source data for
all
the various 16.9 products. I am using completely another file in which I
like to use a cell with the 16.9* to link to. Is that possible?
Otherwise,
I would have to split out various product types. I like to group them.

Make sense??

Well, the first thing *I* would do is rename that file to something much,
much, much shorter!

You can't directly use wildcards in SUMPRODUCT.

In your formula which range contains the 16.9oz?

--
Biff
Microsoft Excel MVP

This is what I have.

=SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$O$2:$O$11818))-SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$M$2:$M$11818))*SUMIF('[WAP''s.xls]WAPs'!$A$2:$A$45,A271,'[WAP''s.xls]WAPs'!$B$2:$B$45)

Are you saying that I need to replace all the A column to "16.9"? I
was
hoping to change the value in my A column like 16.9* meaning all values
with
the 16.9 in front. Does that make sense?

Rich

:

What does compute mean? Count?

Try something like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("16.9oz",A1:A100))))

Or:

=SUMPRODUCT(--(LEFT(A1:A100,6)="16.9oz"))

--
Biff
Microsoft Excel MVP


Hello all,

In using Sumproduct, I have a column that contains similar products
in
which
I need to compute all of. For example, in the Product column, there
are
mulitple 16.9oz products. Can I use 16.9*? I tried, and it didn't
work.
Is
left another option, if so, how would I use it?

Rich