Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can wildcards be used in SUMPRODUCT conditions
I am trying to use SUMPRODUCT to give me the count of the criteria in one
column based on the contents in another column. I have tried to use "*" to represent "text" and I can not get valid results. I am wondering if SUMPRODUCT will allow for wildcards or if maybe I need to use a different function. |
#2
|
|||
|
|||
I don't believe you can use wildcards in Sumproduct, But there are ways
around that if you can give an example of where you would llke to use the wildcard. "Reed" wrote: I am trying to use SUMPRODUCT to give me the count of the criteria in one column based on the contents in another column. I have tried to use "*" to represent "text" and I can not get valid results. I am wondering if SUMPRODUCT will allow for wildcards or if maybe I need to use a different function. |
#3
|
|||
|
|||
I have a column "K" that has either text or numeric entries. I want to use
the "*" as the wild card to count the rows that have text and do not have a corresponding entry in column I of "N/A". I have tried several different entries the latest of which was: =SUMPRODUCT(((K4:K99="*")*(I4:I99<"N/A"))+((K4:K99="1")*(I4:I99<"N/A"))) "bj" wrote: I don't believe you can use wildcards in Sumproduct, But there are ways around that if you can give an example of where you would llke to use the wildcard. "Reed" wrote: I am trying to use SUMPRODUCT to give me the count of the criteria in one column based on the contents in another column. I have tried to use "*" to represent "text" and I can not get valid results. I am wondering if SUMPRODUCT will allow for wildcards or if maybe I need to use a different function. |
#4
|
|||
|
|||
=SUMPRODUCT((ISTEXT(k4:k99)*(NOT(ISNA(i4:i99)))))
"Reed" wrote in message ... I have a column "K" that has either text or numeric entries. I want to use the "*" as the wild card to count the rows that have text and do not have a corresponding entry in column I of "N/A". I have tried several different entries the latest of which was: =SUMPRODUCT(((K4:K99="*")*(I4:I99<"N/A"))+((K4:K99="1")*(I4:I99<"N/A"))) "bj" wrote: I don't believe you can use wildcards in Sumproduct, But there are ways around that if you can give an example of where you would llke to use the wildcard. "Reed" wrote: I am trying to use SUMPRODUCT to give me the count of the criteria in one column based on the contents in another column. I have tried to use "*" to represent "text" and I can not get valid results. I am wondering if SUMPRODUCT will allow for wildcards or if maybe I need to use a different function. |
#5
|
|||
|
|||
The ISTEXT helped with the wildcard situation...Thanks. However the formula
you gave didn't work. The formula that ended up working was: =SUMPRODUCT((ISTEXT(K4:K99)*(I4:I99<"N/A"))+((K4:K99=1)*(I4:I99<"N/A"))) Thanks again. "N Harkawat" wrote: =SUMPRODUCT((ISTEXT(k4:k99)*(NOT(ISNA(i4:i99))))) "Reed" wrote in message ... I have a column "K" that has either text or numeric entries. I want to use the "*" as the wild card to count the rows that have text and do not have a corresponding entry in column I of "N/A". I have tried several different entries the latest of which was: =SUMPRODUCT(((K4:K99="*")*(I4:I99<"N/A"))+((K4:K99="1")*(I4:I99<"N/A"))) "bj" wrote: I don't believe you can use wildcards in Sumproduct, But there are ways around that if you can give an example of where you would llke to use the wildcard. "Reed" wrote: I am trying to use SUMPRODUCT to give me the count of the criteria in one column based on the contents in another column. I have tried to use "*" to represent "text" and I can not get valid results. I am wondering if SUMPRODUCT will allow for wildcards or if maybe I need to use a different function. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcards with SumProduct | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
Multiple SumProduct conditions | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions | |||
Sumproduct Multiple Conditions | Excel Worksheet Functions |