Find Criteria in a Range and Return a Cell

[gtbaseball7]

I have attached what I am trying to do with an example.

Basically, I need to know when the first day A, B, C... does not have a zero (could be negative or positive).

I cannot think of a formula or function that could do this for me but I know it has to be possible.

Thanks all!

[FDibbins]

Try this, copied down...
=IF(COUNTIF(F6:V6,"<>0")>0,SUMPRODUCT(($F$5:$V$5),--(F6:V6<>0)),"")

[gtbaseball7]

You are good, FDibbins. You are good.

What does the "--" mean?

You are much appreciated.

[FDibbins]

F6:V6<>0 returns a series of TRUE FALSE answers. -- coerces those into 1 or 0

[gtbaseball7]

FDibbins,

When I applied this formula to the actual data, I ran into a problem =. I want to return the FIRST day it is not zero. The current formula will only work if it's all zeroes except for one day.

[daddylonglegs]

You can use this formula in D6

=IFERROR(SMALL(IF(F6:V6<>0,F$5:V$5),1),"")

confirmed with CTRL+SHIFT+ENTER and copy down

I'm assuming that by "first date" you mean the earliest one (not the first one positionally, although if they are sorted that will be the same thing)

[gtbaseball7]

the first argument in the if statement (the logical test argument) is an array and returns a #value! error (i took out the iferror to see which error it was)

[FDibbins]

Try this 1 then from Tom Urtis...
=IFERROR(INDEX($F$5:$V$5,MATCH(TRUE,INDEX((F6:V6<>0),0),0)),"")

[gtbaseball7]

This one is it! I have never seen the "TRUE" argument used as the reference. Seems to be working great!

Thank you Dibbins! (and Tom Urtis)

And thank you all that tried to help!

[daddylonglegs]

the first argument in the if statement (the logical test argument) is an array and returns a #value! error (i took out the iferror to see which error it was)

The formula I suggested is an "array formula". You need to confirm with CTRL+SHIFT+ENTER. To do that correctly you put the formula in a cell, press F2 to select the formula and then hold down CTRL and SHIFT keys while pressing ENTER

If you do that correctly you should get correct results. If you don't do that you may see #VALUE! error

Another way, that avoids "array entry" is like this:

=IFERROR(AGGREGATE(15,6,F$5:V$5/(F6:V6<>0),1),"")

See attached for my two suggestions and Ford's - all three return the same results in your sample

[gtbaseball7]

DaddyLL,

You are correct. I need to keep studying these excel nuances. I am not familiar with your technique but it does work.

Thank you!