I am attempting to do a simple countifs formula, which returns a count of how many cells contain the word "unknown". However, I receive the #Value! error each time I try to enter the formula.
It goes as follows:
=COUNTIFS(projects[Project Owner],B1,projects[Stage],A3,projects[#All],"*unknown*")
Projects is the name of the table which is being referenced by the formula.
Any help would be greatly appreciated!
COUNTIFS requires all ranges to be the same size - isn't projects[#All] the full table? That range will be multiple columns and the other ranges are single ones - you can't do that - should the last range be a single column?
Audere est facere
It doesn't like the # in the header #All. Take it out and it'll be fine.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Please Login or Register to view this content.
Regards, TMS
Interesting - thank you for the clarification. Is there a way, then, to execute this function without having to do a countifs formula for each column that I wish to search?
You still have to make sure that the columns are the same height but, using cell reference notation you can use a formula like this
=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*ISNUMBER(SEARCH("unknown",C1:J10)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks