|
Is it possible to use a wild card in a =COUNT(IF equation?
I'm trying to use a wild card in the following equation:
=COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24)) I tested the same equation with out the wild card by replacing it with the exact text and it worked fine. What am I doing wrong? |
Hi
Try the array formula {=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C"),B7:B24 ))} Commit using CTRL+SHIFT+ENTER, do not enter the curly braces yourself, Excel will insert them for you. or the non-array formula =SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C")) Regards Roger Govier JDavis wrote: I'm trying to use a wild card in the following equation: =COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24)) I tested the same equation with out the wild card by replacing it with the exact text and it worked fine. What am I doing wrong? |
That's it! Thanks...
I also discovered that it's possible to increase the text field to "Cont:" but there also needs to be one other change: (LEFT(I7:I24,5)="Cont:"). A "5" needs to be added after the range to designate the length of the text. Thanks again... "Roger Govier" wrote: Hi Try the array formula {=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C"),B7:B24 ))} Commit using CTRL+SHIFT+ENTER, do not enter the curly braces yourself, Excel will insert them for you. or the non-array formula =SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C")) Regards Roger Govier JDavis wrote: I'm trying to use a wild card in the following equation: =COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24)) I tested the same equation with out the wild card by replacing it with the exact text and it worked fine. What am I doing wrong? |
Hi
You're welcome. Yes the LEFT() function is truly LEFT(A1,n) where n is the number of characters you require. If n is omitted, it defaults to 1 which I tend to use when looking for either the first character of a cell or RIGHT() for the last character. (basically I'm lazy and type the minimum characters in a formula!!) From your posting it looked as though you only wanted text beginning with "c". I'm glad you worked out the requirement for 5 in your revised example. Regards Roger Govier JDavis wrote: That's it! Thanks... I also discovered that it's possible to increase the text field to "Cont:" but there also needs to be one other change: (LEFT(I7:I24,5)="Cont:"). A "5" needs to be added after the range to designate the length of the text. Thanks again... "Roger Govier" wrote: Hi Try the array formula {=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C"),B7:B 24))} Commit using CTRL+SHIFT+ENTER, do not enter the curly braces yourself, Excel will insert them for you. or the non-array formula =SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C")) Regards Roger Govier JDavis wrote: I'm trying to use a wild card in the following equation: =COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24) ) I tested the same equation with out the wild card by replacing it with the exact text and it worked fine. What am I doing wrong? |
Hi Roger, I have a follow on question: Is it possible to have three arguments
in this equation? One of them needs to be occurences within a date range. Thanks again, Jason. "Roger Govier" wrote: Hi You're welcome. Yes the LEFT() function is truly LEFT(A1,n) where n is the number of characters you require. If n is omitted, it defaults to 1 which I tend to use when looking for either the first character of a cell or RIGHT() for the last character. (basically I'm lazy and type the minimum characters in a formula!!) From your posting it looked as though you only wanted text beginning with "c". I'm glad you worked out the requirement for 5 in your revised example. Regards Roger Govier JDavis wrote: That's it! Thanks... I also discovered that it's possible to increase the text field to "Cont:" but there also needs to be one other change: (LEFT(I7:I24,5)="Cont:"). A "5" needs to be added after the range to designate the length of the text. Thanks again... "Roger Govier" wrote: Hi Try the array formula {=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C"),B7:B 24))} Commit using CTRL+SHIFT+ENTER, do not enter the curly braces yourself, Excel will insert them for you. or the non-array formula =SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C")) Regards Roger Govier JDavis wrote: I'm trying to use a wild card in the following equation: =COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24) ) I tested the same equation with out the wild card by replacing it with the exact text and it worked fine. What am I doing wrong? |
Hi Jason
The answer is Yes, but I think you need 4 arguments if you want a date range, =lowerdate, <=upperdate. {=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C")*(yourd aterange=DATE(2005,3,1)*(yourdaterange<=DATE(2005 ,6,30),B7:B24))} would give values for dates between 1st March and 30th June 2005. Personally I much prefer the non-array entered SUMPRODUCT solution =SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C"),--(yourdaterange=DATE(2005,3,1),--(yourdaterange<=DATE(2005,6,30)) Regards Roger Govier JDavis wrote: Hi Roger, I have a follow on question: Is it possible to have three arguments in this equation? One of them needs to be occurences within a date range. Thanks again, Jason. "Roger Govier" wrote: Hi You're welcome. Yes the LEFT() function is truly LEFT(A1,n) where n is the number of characters you require. If n is omitted, it defaults to 1 which I tend to use when looking for either the first character of a cell or RIGHT() for the last character. (basically I'm lazy and type the minimum characters in a formula!!) From your posting it looked as though you only wanted text beginning with "c". I'm glad you worked out the requirement for 5 in your revised example. Regards Roger Govier JDavis wrote: That's it! Thanks... I also discovered that it's possible to increase the text field to "Cont:" but there also needs to be one other change: (LEFT(I7:I24,5)="Cont:"). A "5" needs to be added after the range to designate the length of the text. Thanks again... "Roger Govier" wrote: Hi Try the array formula {=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C"),B7 :B24))} Commit using CTRL+SHIFT+ENTER, do not enter the curly braces yourself, Excel will insert them for you. or the non-array formula =SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C")) Regards Roger Govier JDavis wrote: I'm trying to use a wild card in the following equation: =COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24) ) I tested the same equation with out the wild card by replacing it with the exact text and it worked fine. What am I doing wrong? |
Thanks Roger!
"Roger Govier" wrote: Hi Jason The answer is Yes, but I think you need 4 arguments if you want a date range, =lowerdate, <=upperdate. {=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C")*(yourd aterange=DATE(2005,3,1)*(yourdaterange<=DATE(2005 ,6,30),B7:B24))} would give values for dates between 1st March and 30th June 2005. Personally I much prefer the non-array entered SUMPRODUCT solution =SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C"),--(yourdaterange=DATE(2005,3,1),--(yourdaterange<=DATE(2005,6,30)) Regards Roger Govier JDavis wrote: Hi Roger, I have a follow on question: Is it possible to have three arguments in this equation? One of them needs to be occurences within a date range. Thanks again, Jason. "Roger Govier" wrote: Hi You're welcome. Yes the LEFT() function is truly LEFT(A1,n) where n is the number of characters you require. If n is omitted, it defaults to 1 which I tend to use when looking for either the first character of a cell or RIGHT() for the last character. (basically I'm lazy and type the minimum characters in a formula!!) From your posting it looked as though you only wanted text beginning with "c". I'm glad you worked out the requirement for 5 in your revised example. Regards Roger Govier JDavis wrote: That's it! Thanks... I also discovered that it's possible to increase the text field to "Cont:" but there also needs to be one other change: (LEFT(I7:I24,5)="Cont:"). A "5" needs to be added after the range to designate the length of the text. Thanks again... "Roger Govier" wrote: Hi Try the array formula {=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C"),B7 :B24))} Commit using CTRL+SHIFT+ENTER, do not enter the curly braces yourself, Excel will insert them for you. or the non-array formula =SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C")) Regards Roger Govier JDavis wrote: I'm trying to use a wild card in the following equation: =COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24) ) I tested the same equation with out the wild card by replacing it with the exact text and it worked fine. What am I doing wrong? |
| All times are GMT +1. The time now is 01:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com