Tracking "Countif"

[Nephor]

Hi there!
I was googling, but wasn't able to find anything useful. My question is, if there is a possibility to track the "countif"-function. I have a list of people joining a course on different dates. With the countif-function I can count the participants over a certain timeframe. Is there a possibility to track back the result? So, for example, to click the result of 24 people and get the names highlighted?
Thanks in advance!

[Glenn Kennedy]

No... certainly not without VBA, but you can use a formula to copy the matching values to an alternative location. If that is what you want, please refer to the yellow banner (top... there are 2 of them at the moment!!) about sample spreadsheets and attach a sample file, with expected results.

[Nephor]

Hi!
I have attached the excel-file. How to copy the matching files (Group 1 monthly results with Participants names) to an alternate location?
Thanks!

[Glenn Kennedy]

1. Put real dates in F5 copied and format as mmm.

2. Your countifs does not work. Use this in F6, copied across:
=COUNTIFS($B:$B,">="&F$5,$B:$B,"<="&EOMONTH(F$5,0))

3. Use this in P6, copied across and down:
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$6:$A$30)/(TEXT($B$6:$B$30,"mmyy")=TEXT(F$5,"mmyy")),ROWS(P$6:P6))),"")

[Pete_UK]

Another approach would be to use conditional formatting to highlight the names which contribute to the COUNTIFS result. You wouldn't have a separate list of those names.

Hope this helps.

Pete

[Glenn Kennedy]

That's a good suggestion. Why didn't I think of it??

[Nephor]

Thanks! Works for Countif. If I copy P6 across and down in your sheet, it says Participant 1 to 8 in P6 to P13 and so on...

[Nephor]

Hmmm, I tried conditional formating, but doesn't work out... How does that work?

[Glenn Kennedy]

Show me. It was working fine in my file, wasn't it??

[Nephor]

It was correct, but updating cells leads to that...

[Glenn Kennedy]

Open this... It looks OK to me.

[Nephor]

Open this... It looks OK to me.

Nope... Changing Participant 1 to Date 01.09.2021 ==> P6 to W13 is updating to Participant 1 to 8 through August/March
Maybe a Problem Englisch/German?

[Glenn Kennedy]

OK. Now you have EXPLAINED what the problem is...

[Glenn Kennedy]

Works fine for me. It IS a German thing.

1. Try:
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$6:$A$30)/(TEXT($B$6:$B$30,"MMYY")=TEXT(F$5,"MMYY")),ROWS(P$6:P6))),"")

or

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$6:$A$30)/(TEXT($B$6:$B$30,"mmjj")=TEXT(F$5,"mmjj")),ROWS(P$6:P6))),"")

or

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$6:$A$30)/(TEXT($B$6:$B$30,"MMJJ")=TEXT(F$5,"MMJJ")),ROWS(P$6:P6))),"")

[Nephor]

WORKS! Nice! Thanks!

[Glenn Kennedy]

Which one(s) worked?

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[Nephor]

Solution 2 and 3! It was the English-German-translation...
Thanks!

[Glenn Kennedy]

Thanks for the feedback...