Need a counting formula

Hello, I need a formula that will count in a very specific way. Here is an example of the data I am working with.

Example.xlsx

So essentially there is a serial code with many inlet and outlet temperatures. What I need to count is how many unique serial codes have a temperature that goes above 225 and how many have a temperature that goes below 125. The expected results are included in the example spreadsheet.

Let me know if there are any questions.

Thank you!

Hmm?

I might have jumped the gun here, but see if this gives the results you need.

Post #4 takes the Columns individually.

This workbook checks both columns for your conditions simultaneously.

Note The formulae in F2 & I2 are CSE array formulae, and must be confirmed with Ctrl+Shift+Enter before dragging down.

[EDIT]

... I am more looking for a formula that will give me the result straight from the data ...

That is bit more ambitious, and any formula solution will probably drag your workbook down.

How many Rows of data are you likely to have?

Looking at your sample and expected results, I used...
=COUNTIF(B2:C101,">225") = 0
and
=COUNTIF(B2:C101,"<125") =18

Even if I filter BOTH columns on <125 (IE show only rows that contain <125 in BOTH columns), I get 3, not 2 as in your sample? Rows 56, 87 88

Can you explain how you get 2?

@ FDibbins

It's the count of unique serial codes that fail the criteria, not the number of instances that fail.
Hence the additional column in my suggestion in post#6

@ Ferloft that isn't an extra table, it's just a couple of helper columns, truncated to keep the calcs to a minimum, if you need an instant result you will need to use VBa either as a macro or an UDF, a compounded formula will be unreasonably heavy on the workbook.

We could re-jig the layout to use hidden columns if that suits you.

Well hush ma' mouth ...

Nice one mama ..

A word of caution, you can't simply extend the formulae depth by extending the ranges to safely cover your expected largest list of data.

The formulae must cover the exact size of the list, any blank rows at the end of the table, or blanks in the serial codes, will cause the formulae to fail.

To allow for this might I suggest you still use the dynamic names I suggested earlier.

e.g.

Formula:

Please Login or Register  to view this content.

I have added mamas' solution to this workbook (the orange cells)

It looks like Mama's formulas are working but they are extremely slow. I did use the named ranges Marcol but it did not seem to help. I did not want to introduce VBA as that also tends to severely weigh down my spreadsheets.