Excel 2007:
I am trying to create a formula that will read text values in a range(name) and compare them to values in a lookup table. If the value is NOT found in the Lookup table, I want to count the number of occurrences.
Any solutions?
Thanks,
Mike
=SUM(--(COUNTIF(F12:F19,H12:H20)=0))
as an array formula (confirm with ctrl+shift+enter)
As set up, it will return the number of cells in the range H12:H20 that are not contained in the range F12:F19.
Last edited by ragulduy; 03-13-2014 at 11:53 AM.
Thank you sir.
The formula I entered is: =SUM(--(COUNTIF(CommentNames,RawJobs!P:P)=0))
"RawJobs!P:P" is column of entries on a sheet that I want to check against the Range Name Table "CommentNames" to see if they are in this table and give me a count of any that don't match. To test, I have 5 entries in "RawJobs!P:P" that aren't in the table, but the result I get is 0.
I'm unclear as what you mean by confirming the array by using Ctrl-Shift-Enter.
Try this...
Data Range
This formula entered in D2:
=SUMPRODUCT(--ISNA(MATCH(A2:A10,C2:C6,0)))
Do not use the entire column A as a range reference. Use a smaller specific range.
As is that formula will count empty cells in column A. If you do not want the empty cells counted use this version:
=SUMPRODUCT(--(A2:A10<>""),--ISNA(MATCH(A2:A10,C2:C6,0)))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you both. They both worked once I got one of the range cells corrected.
I really appreciate the help I have gotten on this site.
Cheers,
Mike
You're welcome. We appreciate the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks