Sumproduct - use of some characters
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??
"T. Valko" wrote:
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
"MrRJ" wrote in message
...
This is what I have.
=SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS
P9
wkst'!$O$2:$O$11818))-SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B271),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_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
"T. Valko" wrote:
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
"MrRJ" wrote in message
...
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
|