Hello,
I'm trying to write a formula to have excel tell me if (and possibly how many) entries in column E are not part a list. The list is a named range called "Items_List". I've tried several different formulas:
=IF(E:E<>Items_List,1,0)
=COUNTIF(E:E,E:E<>Items_List)
I know these formulas aren't quite right, but I can't figure it out. Any advice?
=COUNTA(E:E)-SUMPRODUCT(COUNTIF(E:E,Items_List))
Try this
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thanks nflsales,
I like where you are going with this, but it didn't quite work, instead it returns #VALUE. I assume that is because the "items_list" part of it refers to text and not numerical data. ANy other ideas?
if you attach a sample file I will now where the problem was
I figured it out. I had the formula on a different sheet and I made a mistake in the sheet reference. Thanks!
Now let me throw another question at you: The items in column E are linked to a date in column D. I would like excel to tell me how many items in column E are not part of Items_List, just like before, but only for those linked to tomorrows date. I've tried a few formulas but I still can't get it:
=COUNTA(E:E)-SUMPRODUCT(COUNTIFS(E:E,Items_List,D:D,NOT(TODAY()+1))
Any ideas here?
Try:
=COUNTA(E:E)-SUMPRODUCT(COUNTIFS(E:E,Items_List,D:D,"<>"&TODAY()+1))
Quang PT
Hey bebo021999,
Thanks for the response. It didn't work. That just counts every entry.
How about change "<>" by "="?
Thanks bebo021999,
I haven't had a chance to look at this forum in a few days. That didn't quite solve my dilemma, but it got me a lot closer. Right now I am using the formula:
=COUNTA(E:E)-SUMPRODUCT(COUNTIFS(E:E,Items_List,D:D,"="&TODAY()+1))-SUMPRODUCT(COUNTIFS(E:E,Items_List,D:D,"<>"&TODAY()+1))
This shows me how many entries there are that are not part of items_list (i.e. custom entries), but it does not show me how many custom entries are made for tomorrow, it just sums ever day. I need an additional part to this formula to remove custom entries (not part of items_list) that are not associated with tomorrow. I've tried:
=COUNTA(E:E)-SUMPRODUCT(COUNTIFS(E:E,Items_List,D:D,"="&TODAY()+1))-SUMPRODUCT(COUNTIFS(E:E,Items_List,D:D,"<>"&TODAY()+1))-SUMPRODUCT(COUNTIFS(E:E,NOT(Items_List),D:D,"<>"&TODAY()+1))
but it still yields the same results as the formula above. Any ideas?
Could you upload a sample worksheet?
Sure thing
The formula's should be showing 1, but they're showing three (depending on when you download this you may need to change some of the dates to tomorrow). I'm aware it's also counting the column header, I was just going to add "-1" at the end of the formula once I figured it out.
I know this may sound like taking a step backwards, but I would use a helper column for this, putting this in , say, I2 and copy down...
=--AND(D2=TODAY(),ISERROR(MATCH(E2,Lists!A:A,0)))
Then just sum that column
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
That works, thanks!
Though it's not ideal. I understand I can just sum all the results to get the total, but the sheet is very dynamic and with a lot of users (people will probably be deleting and adding rows as they go) which means there may be some added rows that do not contain the formula and are neglected. I would prefer if I could find a way to take out the intermediate step of using a helper column and summing, and just have one cell contain a formula to do the work. For now this will work and so I appreciate the help.
If anyone can think of a more direct solution, please let me know!
Another question, what does the "=--" part of the formula do? I can't seem to find an answer by googling.
Thanks!
Last edited by Travisty; 12-07-2015 at 12:37 AM.
=AND() returns a TRUE or FALSE answer. By adding -- you force TRUE to = 1 and FALSE to = 0. You cant add TRUE's, but you can add 1's
Perhaps you could put this all into a Table, then the formulas would get added automatically?
Without helper:
=SUMPRODUCT(ISERROR(MATCH(E:E,Lists!A:A,0))*(D:D=TODAY()))
You can limit E:E or D:D to maximum size, i.e E2:E1000, to save CPU resource.
That's the solution. Thanks a bunch!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks