+ Reply to Thread
Results 1 to 4 of 4

Difficulty with CountIFS using text string

  1. #1
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Difficulty with CountIFS using text string

    Hi everyone,

    I have the following formula:

    =COUNTIFS('Answer Data Month 1'!$BC$2:$BC$15000,LEFT($N9,255)&"*",'Answer Data Month 1'!$AG$2:$AG$15000,G$8,'Answer Data Month 1'!$BD$2:$BD$15000,"Wrong")

    N9 has a long text string which is derived from a data sheet using vlookup.
    G8 is a single word

    There is approximate 30 possible text strings (N9 - N40) with varying content. I'm finding that the expected results are lower than the actual results and I am certain the expected results are correct. Am i correct in assuming that the text string is what's causing me the difficulties? The strings are almost certainly over 255 characters incluidng the vlookup formula. Any ideas how I could search for long text strings more reliabilty?

    Thanks!!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Difficulty with CountIFS using text string

    Try using SUMPRODUCT.

    =SUMPRODUCT(--('Answer Data Month 1'!$BC$2:$BC$15000=$N9),--('Answer Data Month 1'!$AG$2:$AG$15000=G$8),--('Answer Data Month 1'!$BD$2:$BD$15000="Wrong"))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Difficulty with CountIFS using text string

    hi Tony,

    thanks for your reply. Unfortunately I'm getting 0 as a result using the formula (no error though)

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Difficulty with CountIFS using text string

    Maybe this...

    =SUMPRODUCT(--(LEFT('Answer Data Month 1'!$BC$2:$BC$15000,255)=LEFT($N9,255)),--('Answer Data Month 1'!$AG$2:$AG$15000=G$8),--('Answer Data Month 1'!$BD$2:$BD$15000="Wrong"))

    If that doesn't work then we'll need to see some sample data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SUMPRODUCT/COUNTIFS/SUMIFS Difficulty
    By fearonc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-31-2014, 06:41 PM
  2. [SOLVED] Difficulty with fetching data from string with if functions
    By Choson in forum Excel General
    Replies: 7
    Last Post: 12-02-2014, 11:47 PM
  3. [SOLVED] Difficulty with percentage calculation using COUNTIFS/COUNTIF function
    By aldobbs3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-29-2013, 11:38 PM
  4. [SOLVED] Difficulty getting monetry value in 2DP within a text string
    By Deventus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2013, 08:36 AM
  5. [SOLVED] =COUNTIFS with condition based on length of text string
    By sunsoar77 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-25-2012, 02:25 AM

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