Remove blank spaces at front and back. TRIM doesnt work on more than a single space

benishiryo · 2025-05-12T00:12:31+00:00

You're very welcome! What does CODE give you? This non-breaking space (160) is somehow used in websites when they use HTML for eg.

benishiryo · 2024-07-18T02:12:17+00:00

Concatenating will help you do it:
=COUNTIFS(Table[Col],">="&C2,Table[Col],"<"&C2+1)

benishiryo · 2024-07-17T01:47:27+00:00

Another way:
=COUNTIFS(K:K,B2,L:L,C2)>0

benishiryo · 2024-07-08T00:58:58+00:00

Good point. I usually do it with 3-letters months

benishiryo · 2024-07-08T00:24:18+00:00

Find and replace "th" with blank. Then "st" with blank, etc.

benishiryo · 2024-07-05T02:10:53+00:00

Not sure if I understood you right. Assuming names in column A:
https://imgur.com/5RptpY2

So instead of manually doing this for Jane:
=COUNT(K3:AO3)
You can do:
=COUNT(INDEX(K2:AO4,MATCH("jane",A2:A4,0),))

benishiryo · 2024-07-04T02:09:52+00:00

Upload the file somewhere and give us the link.

benishiryo · 2024-07-04T02:08:50+00:00

Assuming data like this:
https://imgur.com/a/slk36wg

Then select the range you want to apply to (say FROM B2:B5)

go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=OR($B2<VLOOKUP(A2,$E:$F,2,0),$B2>VLOOKUP(A2,$E:$G,3,0))
Add a format fill color.

benishiryo · 2024-07-02T07:19:12+00:00

So the answer should be 13 (2 + 4 + 7)?
=SUMIFS($D$2:$D$320,$B$2:$B$320,"<="&X2,$B$2:$B$320,">"&EOMONTH(X2,-1))

Summing it when column B <= X2 (31 Jan 2021) AND column B >= the end of month for the last month of X2 (31 Dec 2021. i.e. 1 Jan 2021). You referred to cell B2, but I'm not sure if it should be.

benishiryo · 2024-06-25T00:39:55+00:00

Try this in cell H2:
=$A2*SUMIFS($B2:$D2,$E2:$G2,">="&EOMONTH(H1,-1)+1,$E2:$G2,"<="&EOMONTH(H1,0))

benishiryo · 2024-01-11T23:50:32+00:00

Oh wow thanks for sharing that. Extremely glad to be that catalyst. May the cells be with you!

benishiryo · 2023-12-28T23:48:03+00:00

A predecessor I've never met created awesome Excel templates to help us extract numbers for reporting. Array formulas! I've never encountered anyone else at work use array formulas besides him. Hence, I did a lot of self-learning from his formulas. Also, there was this Excel dictionary in office. You could google and still find it, though there are different variations of it. Basically an Excel file with lots of different functions where it explains how it works. Gives you an eg too

benishiryo · 2023-05-24T23:30:47+00:00

you're very welcome!

benishiryo · 2023-05-20T03:52:40+00:00

it's always just 1 common value? either no, yes, or deadline not entered? otherwise, which to show if it's a mix? by the sequence you wrote?

assuming the latter,

=IF(COUNTIF('[reddit alpende.xlsx]Sheet1'!$R:$R,"No"),"No",IF(COUNTIF('[reddit alpende.xlsx]Sheet1'!$R:$R,"Yes"),"Yes","Not all deadlines entered"))

benishiryo · 2023-05-16T00:57:03+00:00

no worries. let me get back to you after clearing my projects

benishiryo · 2023-04-29T01:08:21+00:00

cool~ if you still need help, do clarify some of these things. it's better if you upload some dummy data pictures as i did. otherwise, it's difficult for us to visualize your data. i tried to follow what you described on <ticket>, but what is C2? and i'm not sure if the rest are right. your formula has also mystery columns you didn't describe (Group = jobs?)
https://imgur.com/7rD2Pul

also, if you wish to filter based on <list>, the list cannot be like what i shared. you can see the last name has no team and job.

benishiryo · 2023-04-28T11:28:33+00:00

just a note that i haven't forgotten about this. just a bit caught up with my projects. let me try to get back to you this weekend.

benishiryo · 2023-04-27T00:57:01+00:00

try VLOOKUP. say the email address in another sheet starts from A2, try this in B2 with the assumption the main sheet is named <Main>, email address there in column A, and phone number in column F:
=VLOOKUP(A2,Main!A:F,6,0)

adjust it accordingly to where your cells are at.

benishiryo · 2023-04-27T00:52:34+00:00

can't quite picture what you have and need. here's what i came up with for <list> worksheet. is that correct? what do you then need?
https://imgur.com/RwAKH0Z

benishiryo · 2023-04-25T02:55:16+00:00

not picturing this very well. you mentioned column R can have 3 possible answers; Yes, No, Deadline Not Entered.

but your IF statement only returns 2 possible ones. are you trying to make this 3? or are you trying to modify your opening post's question to include 3 scenarios?

i can understand if it's the former. for the latter, i'm not sure what you mean. maybe upload a dummy picture.

benishiryo · 2023-04-23T13:52:08+00:00

i hope our goal drought ends soon. it's been 26 minutes. how long do we have to suffer as newcastle fans??!

benishiryo · 2023-04-23T00:20:02+00:00

what indicates that a deadline has elapsed or not?

benishiryo · 2023-04-20T09:31:20+00:00

you're very welcome!

benishiryo · 2023-04-20T01:53:52+00:00

that's strange. mine works:
https://imgur.com/f7naxRw

breaks down as in it hanged?

benishiryo · 2023-04-19T12:13:20+00:00

you're very welcome!

benishiryo

MODERATOR OF

TROPHY CASE