Can wildcards be used in SUMPRODUCT conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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")))
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

wildcards in sumproduct functions 11
Really need some help with a multi conditional SumProduct 1
wild card in SUMPRODUCT formula 7
Wildcards in SUMPRODUCT 3
SUMPRODUCT, COUNTIF and wildcard 3
SumProduct w/WildCard 4
SUMPRODUCT - Count Various criteria in same column (exclude other) 8
Fun with SUMPRODUCT 14

Back
Top