+ Reply to Thread
Results 1 to 4 of 4

=IF(COUNTIF) Formula I have on database does not work when i sort. HELP PLEASE !!

  1. #1
    Registered User
    Join Date
    04-18-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    1

    =IF(COUNTIF) Formula I have on database does not work when i sort. HELP PLEASE !!

    Hello, I am having issues with an excel sheet i have created...

    On Sheet 1 I have my master report, I run a report daily and import the data into this on Sheet 2 & 3 i cut and paste data from other report sources here.

    I have the following formula to tell me if the data on my master report matches data on other sheets - =IF(COUNTIF('Completed Report'!$G$2:$G$5000,'QM Report'!P2)>0,"Matched","No Match").

    I input data in bottom of master sheet and apply formula this works perfect, but when i sort the sheet into a specific order the formulas for the cells change and dont give me the correct answer can you help with this ??

    Many Thanks
    Sean

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: =IF(COUNTIF) Formula I have on database does not work when i sort. HELP PLEASE !!

    Providing a small sample spreadsheet with the working state, and then directions on how the sort makes it fail would help. This is probably not the specific issue, but I would probably use MATCH instead of COUNTIF. They may take the same amount of time, but MATCH better states the intent of your formula.

    What is likely happening is that after your sort, the formula references moved with the sort. Such that the 'QM Report'!P2, which you expected to 'stay on the same row' after the sort, actually got moved to a different row but still says 'QM Report'!P2.

    Since you have Excel 2010, use a Table. Highlight your data and then hit Ctrl-T. Excel will create a Table. Then re-enter that calculation for determining the match, but use the mouse to select the cells you want to use in the equation (i.e. click on cell P2 instead of manually typing in "P2"). This should create something like this:
    =IF(COUNTIF('Completed Report'!$G$2:$G$5000,Table1[[#This Row],[YourTableHeader]])>0,"Match","No Match")
    or if you switch to MATCH
    =IF(ISNA(MATCH(Table1[[#This Row],[YourTableHeader]],'Completed Report'!$G$2:$G$5000,0)),"Not Matched","Matched")

    Either way, if you enter the formula in the top row of the table, Excel will magically change the cells below to the new formula (since it is now a Table). Also, since the equation now references Table1[[#This Row],[YourTableHeader]], it will always pull the data from that row, even when the table is sorted or filtered.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: =IF(COUNTIF) Formula I have on database does not work when i sort. HELP PLEASE !!

    Quote Originally Posted by delwatson View Post
    =IF(COUNTIF('Completed Report'!$G$2:$G$5000,'QM Report'!P2)>0,"Matched","No Match")
    Hello Sean, which sheet is this formula on?
    Audere est facere

  4. #4
    Forum Contributor
    Join Date
    12-11-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    232

    Re: =IF(COUNTIF) Formula I have on database does not work when i sort. HELP PLEASE !!

    I make a mock up of whay you described, and it works no problem. I'm interested to hear more.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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