I have a column with data (mostly names) that looks like this...
Joe Tom Microsoft Tom Sue Mark Mark
I would like for it to ignore empty/blank cells so I end up with a column that looks like:
Joe 1 Mark 2 Microsoft 1 Sue 1 Tom 2
Any help would be appreciated. Please note that in the preview, the table did not show distinct blank/empty rows.
list unique list Joe Joe Tom Mark Microsoft Microsoft Sue Tom Tom Sue Mark Mark
D2=IFERROR(INDEX(A$2:A$100,MATCH(0,INDEX(COUNTIF(A$2:A$100,"<"&A$2:A$100)-SUMPRODUCT(COUNTIF(D$1:D1,A$2:A$100)),),0)),"")
Copy down
E2=IF($D2<>"",COUNTIF($A$2:$A$100,$D2),"")
copy down
Caracalla,
It works perfectly! I would like to expand on what you provided. If column B had a status like 'open' or 'closed, and F1 was 'open' and G1 was 'closed', how can I use D2 to then count how many instances of Mark are open/closed? It should know that if column A5 or D8 are empty, then not to count anything.
Thinking something like: if A2:A11 = D2 then count where B2:B11 = F1
Last edited by mainemojo; 10-15-2019 at 02:42 PM.
list unique list OPEN CLOSED Joe OPEN Joe Tom CLOSED Mark Microsoft OPEN Microsoft Sue Tom CLOSED Tom Sue CLOSED Mark OPEN Mark CLOSED
F2=IF($D2<>"",COUNTIFS($A$2:$A$100,$D2,$B$2:$B$100,F$1))
Copy across and down
I finally figured out the COUNTIFS portion, but couldn't figure out how to add the "". I had to change the formula to
F2=IF($D2<>"",COUNTIFS($A$2:$A$100,$D2,$B$2:$B$100,F$1),"")
If I didn't add the ,"" at the end, I would have ended up with FALSE in F7:G11.
Last edited by mainemojo; 10-15-2019 at 03:00 PM.
Sorry
I forgot "" in the formula
F2=IF($D2<>"",COUNTIFS($A$2:$A$100,$D2,$B$2:$B$100,F$1),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks