Formula to evaluatdata from last row of a sheet of data

Filomax

Hi Dears,

I have a form that completes the data of per date with the status of the asset per column, therefore I'm looking to using countif the different status, but I need that the formula evaluates the last row which should be the lastest info updated based on the latest date. I'm trying to use INDEX or MAX but I didn't able to make it work. I'll appreciate your support.

Genevieve P.

Hi @Filomax

You're on the right track with Index and Match. You'll just need to make sure the first range in your INDEX function covers all columns that you're looking through. This is the structure I would use:

=COUNTIFS(INDEX(Entire Range across all columns, MATCH(Max Date, Date Column, 0)), Status to the left)

So in your sheet, try something like this:

=COUNTIFS(INDEX(Date:[G8], MATCH(MAX(Date:Date), Date:Date, 0)), Status@row)

Let me know if that works!

Cheers,

Genevieve