| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT, COUNTIF and wildcard
Biff, I think I should start my own thread ......
It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT. =COUNTIF(C:C,"A*") or =COUNTIF(C1:C1000,"A*") We can't use column nor wildcard with SUMPRODUCT for version 2003. (As per Biff, okay to use column for 2007.) So, we have to use LEFT( ). =SUMPRODUCT(--(LEFT(C1:C1000)="A")) Now, if I want to use COUNTIF and my criterion is "A*" when "*" is not wildcard this time, how do I tell Excel? The tilde sign "~" seems to work. Can someone confirm, please? =COUNTIF(C:C,"A~*") or =COUNTIF(C1:C1000,"A~*") or =SUMPRODUCT(--(LEFT(C1:C1000,2)="A*")) If I want "A**" is there an easier way than A~*~* Thanks! Epinn |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, COUNTIF and wildcard
If I want "A**" is there an easier way than A~*~*
I think that's about as easy as it can get! It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT. You can use wildcards in other functions (that support wildcards) within Sumproduct: ..................A..............B.............C 1..........A1234...........Y............10 2..........A4321...........Y............22 3..........A1123...........Y............37 Criteria: sum column C where column B = "Y" and column A = "A<any_single_character1" =SUMPRODUCT(--(ISNUMBER(SEARCH("a?1",A1:A3))),--(B1:B3="Y"),C1:C3) Returns: 37 =SUMPRODUCT(--(ISNUMBER(SEARCH("a*1",A1:A3))),--(B1:B3="Y"),C1:C3) Would return the incorrect result. It would include EVERY entry from column A as meeting the search criteria. Biff "Epinn" wrote in message ... Biff, I think I should start my own thread ...... It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT. =COUNTIF(C:C,"A*") or =COUNTIF(C1:C1000,"A*") We can't use column nor wildcard with SUMPRODUCT for version 2003. (As per Biff, okay to use column for 2007.) So, we have to use LEFT( ). =SUMPRODUCT(--(LEFT(C1:C1000)="A")) Now, if I want to use COUNTIF and my criterion is "A*" when "*" is not wildcard this time, how do I tell Excel? The tilde sign "~" seems to work. Can someone confirm, please? =COUNTIF(C:C,"A~*") or =COUNTIF(C1:C1000,"A~*") or =SUMPRODUCT(--(LEFT(C1:C1000,2)="A*")) If I want "A**" is there an easier way than A~*~* Thanks! Epinn |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, COUNTIF and wildcard
Biff,
Thank you for taking the time to teach me. So, other than COUNTIF, SEARCH can take wildcard. Any other functions, off hand? =SUMPRODUCT(--(ISNUMBER(SEARCH("a?1",A1:A3))),--(B1:B3="Y"),C1:C3) << The wildcard ? follows the general rule. =SUMPRODUCT(--(ISNUMBER(SEARCH("a*1",A1:A3))),--(B1:B3="Y"),C1:C3) << Looks like a*1 is treated as a* Cheers, Epinn "Biff" wrote in message ... If I want "A**" is there an easier way than A~*~* I think that's about as easy as it can get! It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT. You can use wildcards in other functions (that support wildcards) within Sumproduct: ..................A..............B.............C 1..........A1234...........Y............10 2..........A4321...........Y............22 3..........A1123...........Y............37 Criteria: sum column C where column B = "Y" and column A = "A<any_single_character1" =SUMPRODUCT(--(ISNUMBER(SEARCH("a?1",A1:A3))),--(B1:B3="Y"),C1:C3) Returns: 37 =SUMPRODUCT(--(ISNUMBER(SEARCH("a*1",A1:A3))),--(B1:B3="Y"),C1:C3) Would return the incorrect result. It would include EVERY entry from column A as meeting the search criteria. Biff "Epinn" wrote in message ... Biff, I think I should start my own thread ...... It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT. =COUNTIF(C:C,"A*") or =COUNTIF(C1:C1000,"A*") We can't use column nor wildcard with SUMPRODUCT for version 2003. (As per Biff, okay to use column for 2007.) So, we have to use LEFT( ). =SUMPRODUCT(--(LEFT(C1:C1000)="A")) Now, if I want to use COUNTIF and my criterion is "A*" when "*" is not wildcard this time, how do I tell Excel? The tilde sign "~" seems to work. Can someone confirm, please? =COUNTIF(C:C,"A~*") or =COUNTIF(C1:C1000,"A~*") or =SUMPRODUCT(--(LEFT(C1:C1000,2)="A*")) If I want "A**" is there an easier way than A~*~* Thanks! Epinn |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, COUNTIF and wildcard
So, other than COUNTIF, SEARCH can take wildcard.
Any other functions, off hand? Match, V/Hlookup, Find, Sumif There may be one or two others that I'm forgetting. Biff "Epinn" wrote in message ... Biff, Thank you for taking the time to teach me. So, other than COUNTIF, SEARCH can take wildcard. Any other functions, off hand? =SUMPRODUCT(--(ISNUMBER(SEARCH("a?1",A1:A3))),--(B1:B3="Y"),C1:C3) << The wildcard ? follows the general rule. =SUMPRODUCT(--(ISNUMBER(SEARCH("a*1",A1:A3))),--(B1:B3="Y"),C1:C3) << Looks like a*1 is treated as a* Cheers, Epinn "Biff" wrote in message ... If I want "A**" is there an easier way than A~*~* I think that's about as easy as it can get! It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT. You can use wildcards in other functions (that support wildcards) within Sumproduct: ..................A..............B.............C 1..........A1234...........Y............10 2..........A4321...........Y............22 3..........A1123...........Y............37 Criteria: sum column C where column B = "Y" and column A = "A<any_single_character1" =SUMPRODUCT(--(ISNUMBER(SEARCH("a?1",A1:A3))),--(B1:B3="Y"),C1:C3) Returns: 37 =SUMPRODUCT(--(ISNUMBER(SEARCH("a*1",A1:A3))),--(B1:B3="Y"),C1:C3) Would return the incorrect result. It would include EVERY entry from column A as meeting the search criteria. Biff "Epinn" wrote in message ... Biff, I think I should start my own thread ...... It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT. =COUNTIF(C:C,"A*") or =COUNTIF(C1:C1000,"A*") We can't use column nor wildcard with SUMPRODUCT for version 2003. (As per Biff, okay to use column for 2007.) So, we have to use LEFT( ). =SUMPRODUCT(--(LEFT(C1:C1000)="A")) Now, if I want to use COUNTIF and my criterion is "A*" when "*" is not wildcard this time, how do I tell Excel? The tilde sign "~" seems to work. Can someone confirm, please? =COUNTIF(C:C,"A~*") or =COUNTIF(C1:C1000,"A~*") or =SUMPRODUCT(--(LEFT(C1:C1000,2)="A*")) If I want "A**" is there an easier way than A~*~* Thanks! Epinn |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF with wildcards | Excel Worksheet Functions | |||
| Need help on countif and sumif function with dates and wildcard characters | Excel Discussion (Misc queries) | |||
| Countif using Wildcard * and number 1 | Excel Worksheet Functions | |||
| wildcard in sumproduct? | Excel Worksheet Functions | |||
Linear Mode
