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.

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.

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.

=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.

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.