+ Reply to Thread
Results 1 to 3 of 3

countif 2 cells are equal, when 1 cell is equations that could be letters or numbers

  1. #1
    Registered User
    Join Date
    09-22-2011
    Location
    northumberland
    MS-Off Ver
    Excel 2003
    Posts
    6

    countif 2 cells are equal, when 1 cell is equations that could be letters or numbers

    I thought this would be easy enough to do but after a couple of days messing around on and off with formula's i am no closer to getting the result i want:

    I have 2 rows of cells being compared using the formula:
    =IF(E5>F5,"1",IF(E5<F5,"2", IF(E5=F5,"X")))

    This works fine but then my next formula is suppose to count if this matches another range of cells (e.g. cells I-L 5:16), i tried:
    =SUMPRODUCT((K5:K16=M5:M16)*1)
    But this only works for the x's unless i use the abs() function which throws an error any time I have x's.

    I am at a bit of a loss now so any assistance would be kindly received
    Last edited by Miguell; 09-28-2011 at 12:08 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: countif 2 cells are equal, when 1 cell is equations that could be letters or numb

    not sure if it fixes the exact issue, but the 1 and 2 in the formula should probably not be within quotes, if you want actual numbers.

    e.g.

    =IF(E5>F5,1,IF(E5<F5,2, IF(E5=F5,"X")))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    09-22-2011
    Location
    northumberland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: countif 2 cells are equal, when 1 cell is equations that could be letters or numb

    You are some kind of magician!

    That sort everything just nicely. Thanks

+ 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