Question about Microsoft Excel for PC

1 Answer

Cannot get COUNTIF to work! Here is my situation: I want to prevent duplicate entries of a loan number by the user. c has my loan numbers, and I want to prevent users from entering a loan number that has already been entered. Here is the formula I am using under Data/Validation/Custom: COUNTIF($C$2$C$999,C2)=1 My header is located in C1, data begins in C2. Using this, it will not allow me to enter ANY data whatsoever without popping up the stop box. How do I fix????

Posted by on

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that gotĀ 5 achievements.

    Governor:

    An expert whose answer gotĀ voted for 20 times.

    Scholar:

    An expert who has written 20 answers of more than 400 characters.

  • Expert
  • 145 Answers

The only thing I see wrong in your formula is a missing colon.

COUNTIF($C$2:$C$999,C2)=1

Posted on Mar 15, 2009

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Countif formulas


Are you trying to find how many don't have a letter? If so you might try "COUNTA" instead of "COUNTIF". You could also try looking at the format of the cell your formula is in and change it to general or number.

Jul 13, 2014 | Business & Productivity Software

1 Answer

Countifs formula


COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition.
This COUNTIF formula appears to count only the Employees that have the value "F" in the designated container. ie. Female.

Nov 20, 2013 | Microsoft Excel 2010

1 Answer

Sum of the greatest run of negitive numbers


Hi, Not sure if this is what you have tried:
1: Assuming each run is on a different column
Run1 Run2 Run3 Run4, etc...and the maxium lenght of each column is 9 values, but each colum has a different lenght of numbers..
Then you can use:
=MAX(COUNTIF(A2:A10,"<>0"),COUNTIF(B2:B10,"<>0"), {keep adding countif for each colum).

If all runs are on the same colum, then using Pivot tables is easier. If you have a copy of your data, please post it so that I can give you the exact formula ;)

Oct 21, 2009 | Microsoft Excel for PC

2 Answers

Copy formulas


It should work if you just copy and paste it. Or select the range of your working area and right click in destination cell and choose paste special. When new window pop up, just choose Formula.

It should work usually, but by any chance you've encountered the problem regarding this. Just let me know...i'll try to help you...


P.S.: If this information was helpful, please rate this solution.

Mar 23, 2009 | Microsoft Office Excel 2003 for PC

2 Answers

FORMULA TO COMPLETE A COUNT TO A PERCENTAGE


Apply the following:
COUNT(A3:A74)/COUNTIF(A3:A74,"1")*100 in order to obtain the percentage.

Nov 26, 2008 | Microsoft Office 2003 Basic Edition...

1 Answer

To cancel duplicatetel nos in next list any formula?


you can use countif (phonelist range, phone number)

any result > 1 has a duplicate. :)

Oct 12, 2008 | Microsoft Business & Productivity Software

3 Answers

Excel Formula


Lets say your data (cars) is in range "D1:D21".

You need to know how many are Dodge and Ford in cell E1.

=COUNTIF(D1:D21,"Dodge") + COUNTIF(D1:D21,"Ford)

Hope this helps.

Apr 22, 2008 | Microsoft Excel for PC

2 Answers

Duplicacy in excel sheet


Since you are searching the data by the phone number , first select all the data in the spreadsheet and sort it in ascending order by the phone number.
Then, assuming you have 5 columns of data A through E, and the phone numbers are in column E, with row 1 occupied by column headings, use the following formula in cell F2=IF(E2=E1,"Duplicate",1)

Drag this formula down column F till the end of your data
Select the entire data and do an auto filter
In column F filter the data by Duplicate and delete all these rows
What remains should be unique data

Dec 19, 2007 | Business & Productivity Software

1 Answer

Excel problem


Have you tried countif?

= countif(c:c, "Yes")

The "Yes" is CASE SENSITIVE!

You may also have to do a combo:

= count(b:b) - countif(c:c "<>'Yes'")

Wher the 'Yes' is in single quotes and the entire condition is in double quotes.

Dec 05, 2007 | Business & Productivity Software

1 Answer

Excel


See if this is possible using the COUNTIF formula in Excel. Example: My range is A1:A20. Lowest number being 1 and highest number being 20. To see how many are less than 10 I'd use: =COUNTIF(A1:A20,"<=10") To see how many are less than 20 but greater than 10 is a little different. (And this might be the crazy way). I do this: =SUM(COUNTIF(A1:A300,"<=20") - COUNTIF(A1:A300,"<=10")) Try this and see if it works for you. It seemed to work fine for me.

Sep 23, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Microsoft Excel for PC Logo

119 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18298 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...