count the absence of nmber in range of cells

Microsoft Microsoft office excel 2007 ac...
January 21, 2010 at 07:34:36
Specs: Windows XP
I have a range of cells where I need to count the
number of times that a number is not present. I
need to do the same for text.

See More: count the absence of nmber in range of cells

Report •


#1
January 21, 2010 at 07:46:38
First, it comes across much friendlier if you actually ask for help instead of just telling us what you need.

Second, the answer to your question is 1.

If a value is not present, it can only be not present once.

How could something not be there multiple times?


Report •

#2
January 21, 2010 at 07:56:01
I apologize for the lack of decorum. I appreciate your trying to
help me.

To answer the question in your reply, It could be absent a
number of times in a range of cells. A range of A132:A750
may have a number of times [or cells] where an entry might
not be present. This is because the cell entries are manually
entered, and may not fit a series of criteria that are easily
counted using the count function. Entries that are typos or not
spelled correctly would b captured by this also. Does that
help? Many Thanks!


Report •

#3
January 21, 2010 at 08:15:03
I still need some clarification.

re: "where an entry might not be present"

Are you referring to blank cells within the range or a specific value that can't be found?

re: Entries that are typos or not spelled correctly would be captured by this also.

What are you comparing the entries to in order to determine if they are typos or not?

Please give us an example of your data, including what you might expect as a count of "entries that are not present" and "entries that are typos" based on that example.

Perhaps that would clarify the question for us.


Report •

Related Solutions

#4
January 21, 2010 at 08:25:45
Thanks for your continued help!

re: "where an entry might not be present"
It would be a specific value that can't be found

re: Entries that are typos or not spelled correctly would be captured
by this also.
I'm not really looking for typos, but for entries that are not standard.
The standard entries are made from a menu [list], but sometimes
the person entering the info determines that the required entry info
doesn't match one of the list items, so we end up with a non-
standard entry that doesn't match one of the criteria we use in a
COUNTIF. I was hoping that there was some character we could
use to catch those non-standard entries in that statement. Am I all
wet in thinking that?


Report •

#5
January 21, 2010 at 08:49:06
Can you use Data Validation to ensure that no non-standard entry can be made or do you need to allow for non-standard entries?

If you do, are you asking for a count of items in your range that are not on the list of standard entries?

re: It would be a specific value that can't be found

Again, I'm confused by this statement.

Let's say the specific value in question is "47". Your range consists of 619 cells. Are looking for a count of how many of those cells do not contain 47, which could be anywhere from 0 if every cell contained 47 to 619 if no cells contained 47?

Once again, I think some sample data showing us the type of results you are looking for might be useful.


Report •

#6
January 21, 2010 at 10:20:58
I apologize. I didn't communicate very well. My use of the word
specific has caused the confusion.

I do need to allow for non-standard entries, and I am asking for a
count of items in the range that are not on the list of standard
entries. So if the list is Std Notebook, Std Desktop, Std
Workstation, and someone enters "Lenovo Netbook" or "Dell
Inspiron", I need to be able to count those entries as non-
standard

Thank you!


Report •

#7
January 21, 2010 at 10:57:04
OK, maybe this will get you what you need:

Assuming the Manual Entries are made in A1:A100 and your list of Standard Entries are in A132:A750, try this Array formula:

=SUM(1*NOT(IF(ISERROR(MATCH(A1:A100,A132:A750,0)),,1)))

After typing or pasting this formula in the formula bar, you have to enter it using Ctrl-Shift-Enter to add the array brackets { } around it. You can't just type the { } to create an array formula.

Every time you edit the formula, or click in the formula bar, you have to use Ctrl-Shift-Enter again.


Report •

#8
January 21, 2010 at 11:01:18
Many thanks for help with the syntax and logic!

Report •

#9
January 21, 2010 at 11:04:40
Does it work?

Report •

#10
January 21, 2010 at 11:06:42
Yep, it sure does!

Report •


Ask Question