| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - use of some characters
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - use of some characters
If i've understood correctly, try this
=SUMPRODUCT(--(LEFT(A1:A10,4)="16.9")) Mike "MrRJ" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - use of some characters
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. -- Rick (MVP - Excel) "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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - use of some characters
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - use of some characters
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. On Oct 7, 6:54*pm, MrRJ wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - use of some characters
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - use of some characters
Thanks Pete,
Question. Can I change the column A instead of typing in the 16.9 in the formula? Rich "Pete_UK" wrote: 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. On Oct 7, 6:54 pm, MrRJ wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - use of some characters
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? "Mike H" wrote: If i've understood correctly, try this =SUMPRODUCT(--(LEFT(A1:A10,4)="16.9")) Mike "MrRJ" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - use of some characters
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - use of some characters
I like to use a cell with the 16.9* to link to.
You can't directly use wildcards in SUMPRODUCT. 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 "MrRJ" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| Sumproduct with wildcard characters? | Excel Discussion (Misc queries) | |||
| In Excel find characters when multiple characters exist w/i a cel | Excel Worksheet Functions | |||
| HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
| Using Wildcard characters in sumproduct | Excel Worksheet Functions | |||
Linear Mode
