Forum Discussion

Copper Contributor

Jan 26, 2024

How to check if all cells in a non congruent range/list meet specific criteria. See below.

I am trying to use the following formula to see if 1. All 39 cells in row C have data, 2. All cell values are greater than 225, and 3. All cell values are less then 384. If all conditions are met, t...

excel

Patrick2788

Silver Contributor

Jan 29, 2024

spinaz0070

I offer a 365 solution:

=LET(
    rng, C17:C93,
    wrapped, WRAPROWS(rng, 2, ""),
    vector, TOCOL(TAKE(wrapped, , 1), 1),
    tally, COUNT(vector),
    total, SUM(vector),
    IF(AND(tally = 39, total > 225, total < 384), "Pass", "Fail")
)

mathetes

Gold Contributor

Jan 29, 2024

Patrick2788

Thank you for that solution!

I had wondered if there was a way to create a vector like that, using LET and a couple of intervening steps. I've never used those three functions--WRAPROWS, TOCOL and TAKE.

Thanks for showing the how to.