+ Reply to Thread
Results 1 to 6 of 6

COUNTIF or SUMPRODUCT for text value/criteria with repeats HELP!

  1. #1
    Registered User
    Join Date
    02-20-2013
    Location
    michigan
    MS-Off Ver
    Excel 2007
    Posts
    14

    COUNTIF or SUMPRODUCT for text value/criteria with repeats HELP!

    Ok...so, I basically want a text value "Interested" to count into a TOTAL column.

    I don't know if I use SUMPRODUCT or COUNTIF for the entire row and column of E.

    This is what I have right now:
    =COUNTIF(E:E,"*Interested*")

    It works...EXCEPT for the fact that I also have a text value "Not Interested" in the data as well...

    So, my problem is that if I type in Not Interested, it also counts into the Interested column. How do I prevent this from happening?

    I've also tried...a sumproduct, but I'm not sure....if I did it right.

    Also, like I said...I mean if there is a way to make "Not Interested" a single criteria but keep the space..that might work? Or, how can I just basically make Excel not take "Not Interested" into the TOTAL column as both "Interested" AND "Not Interested".

    THanks everyone.

  2. #2
    Registered User
    Join Date
    02-20-2013
    Location
    michigan
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: COUNTIF or SUMPRODUCT for text value/criteria with repeats HELP!

    Sorry if that was confusing. But one of the big problems is "Not Interested" counts into the "Interested" TOTAL column...not the other way around....

    So, "Interested" never goes into the TOTAL column for "Not Interested"

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: COUNTIF or SUMPRODUCT for text value/criteria with repeats HELP!

    How important are the * characters? The formula is interpreting these as wildcards. If you can do without them or change them for something else, your formulae should work as expected.
    Martin

  4. #4
    Registered User
    Join Date
    02-20-2013
    Location
    michigan
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: COUNTIF or SUMPRODUCT for text value/criteria with repeats HELP!

    the * characters are important just in case I have to put other words in there. So like...."called and emailed, interested"

    I should have mentioned that I also have a CF: =SEARCH("interested",INDIRECT("E"&ROW()))=1
    for each of the terms to "highlight/color" the rows.....These seem to be ok......

  5. #5
    Registered User
    Join Date
    02-20-2013
    Location
    michigan
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: COUNTIF or SUMPRODUCT for text value/criteria with repeats HELP!

    So, if I do take out the * characters...the highlights/colors go away...

    and if I add the * into my CF : =SEARCH("interested",INDIRECT("E"&ROW()))=1

    then...I have another problem where "not interested" is green, instead of red.

  6. #6
    Registered User
    Join Date
    02-20-2013
    Location
    michigan
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: COUNTIF or SUMPRODUCT for text value/criteria with repeats HELP!

    Someone please help...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1