Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Countif function giving incorrect value

0


=IF(COUNTIFS(Entries!$B$10:$B$104,A2)>=1,"",ROW())

On the "Presenatation Data" sheet  I am using a COUNTIF function to remove , previously used id numbers to avoid duplicates. But the formula that I am using has lost the 1st number in the list ie.1 which has been used and consequently the 2nd column does not read correctly against the preliminary column. The (Entries!$B$10:$B$104) refers to the target sheet that the application forms are recorded on, If I put A1 in place of a2 I get the same error. Help appreciated . Thanks Jim

excel
MS Office Version: Office 2019
posted by: Jim W (rep: 14) on Tue Feb 12, 2019 at 7:14 am
Answer
Discuss

Answers

1
Selected Answer

I believe that your Header in Column A throws off the Rows by a factor of '1'.  Try subtracting 1 from your formula. in cell B2 I used ..  

=IF(COUNTIFS(Entries!$B$10:$B$104,A2)>=1,"",ROW()-1)
posted by: queue (rep: 467) on Tue Feb 12, 2019 at 8:47 am
Discuss

Discussion

Brilliant ! Spot On! Thanks very much, I can now move on. 
Jim W (rep: 14) Feb 12, '19 at 9:10 am
Sorry as you can see I have removed the Question
Jim W (rep: 14) Feb 13, '19 at 9:51 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login