=COUNTIFS to return only the first instance

[MM86]

Hi,

1. Experiments are performed over 3 days. Dates are in column A.
2. Each day has 10 numbered experiments. Experiments’ numbers are in column B.
3. Each experiment has outcome expressed in %. Percentages are in column C.
4. Each experiment has threshold which is fixed for all experiments and expressed as % in cell: B2.
5. =IF() function evaluates if experiment reached threshold or not. Outcomes are in column D.

What I’m trying to do is have a function (after googling-ing and chatGPT-ing it, it was suggested to use =COUNTIFS ), that shows ONLY the first instance when threshold was reached each day. So in the example on the screenshot I want column D to look like column E.

update: attached sample as requested
Experiment_sample_2.ods (23.0 KB)

[PKG]

@MM86 ,
Make life easier for everyone and attach a sample file (.ods).

[MM86]

attached

[karolus]

There are two problems:

• 1. The Date in Column A is NOT repeated in each row until the next Date…
• 2. The Date from A4 IS repeated in A24?

[MM86]

1. Correct: the Date in column A is not repeated until the next Date. But I guess it can be if it would make things easier.
2. Typo: A4 should be 08/12/25. I corrected both: screenshot and the sample.

[Lupp]

See attached file:
disask_129866_ExperimentRe.ods (19.6 KB)

Please note: An example document attached here shouldn’t contain links to external files.

[MM86]

Thank you @Lupp
In your attached file, if a threshold is changed (B1), the values change in column D, but not in your formulas.
Regarding this links to external files - not sure where are they. I can see that if I open my sample file, there’s a prompt that links have been disabled, but I have no idea what links - I can’t find any. Nonetheless, I remade the sample file to follow your note.

[Lupp]

The results change for me as expected.
Did you disable >Data>Calculate>AutoCalculate ?
What about Ctrl+Shift+F9?

[MM86]

I re-downloaded your file.
I didn’t disable anything: AutoCalculate is on.
I have no idea what Ctrl+Shift+F9 does, but I pressed it and the results still don’t change. Any1 else having problem with @Lupp file or just me?

[Lupp]

No clue. I also tested with the download using the link in my post above.

Ctrl+Shift+F9 should by default force a full recalculation.

Somebody else getting the OQer’s issue?

[MM86]

lol, my bad. I was just entering values that weren’t changing the outcome not that the formulas didn’t work

[karolus]

Hello
three “helpers” in F1:F3

=MINIFS(B4:B13;C4:C13;">="&B1)
=MINIFS(B14:B23;C14:C23;">="&B$1)
=MINIFS(B24:B33;C24:C33;">="&B$1)

… starting in D4 with:

=IF(B4=$F$1;"Y";"-")  #pull down to D13

… starting in D14 with:

=IF(B14=$F$2;"Y";"-")  #pull down to D23

… starting in D24 with:

=IF(B24=$F$3;"Y";"-")  #pull down to D33

[MM86]

@karolus awesome, it works ! thanks. Would it be too much of me if I ask if you could explain these “helpers”? What do they do, how they work? The spreadsheet is going to be evolving, more dates with more experiments so I’m assuming I’d have to add “helper” for each additional day of experiments?

[karolus]

the helpers simply returns the lowest number from B where the condition (C? greater_equal to B1) is True

but the solution doesnt really scale with a lot of more dates and|or different numbers of experiments

[MM86]

haha ok, thanks anyway In that case I hope you don’t mind if I mark @Lupp post as a solution

[karolus]

@MM86

post as a solution

no problem… but …
keep sure you repeat each date until next date and use in D4:

=IF(B4=MINIFS(B$4:B$1000 ; C$4:C$1000;">="&$B$1 ; $A$4:$A$1000;A4) ;"Y";"-")

see
ask_129866.ods (24.5 KB)