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

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. ## 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.

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

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

4. ## 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.

##### Users Browsing this Thread

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

#### 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