Return the last value with a number

Jennifer C

I have a sheet that I need to total at the top, but for a few columns, the "total" is not a sum or a max. It is a number that can go up or down each week and I just want the most current weeks data to show at the top. I have tried a few of the Smartsheet helps, but none of them are giving me exactly what I want. A screenshot is below. The number at the top, what I am trying to get the formula to show me. Again the number will go up or down each week.

Paul Newcome

Give this one a go...

=INDEX(COLLECT(CHILDREN(), CHILDREN(), ISNUMBER(@cell)), COUNTIFS(CHILDREN(), ISNUMBER(@cell)))

Alexander Orsini

Hi @Jennifer C , I had a similar challenge. What I ended up doing was creating a formula in the sheet summary that was driven by multiple criteria to determine inclusion of want I want counted or summarized. I then referred to that sheet summary value to display the value in the top record. You then can add rows and move rows below the top level, the sheet summary formula works to figure out what to include, and the top row just displays the value. This is an example using countifs to check for multiple conditions (ANDs and ORs).

=COUNTIFS([Request Status]:[Request Status], OR(@cell = "Approved", @cell = "Successfully Completed"), [Strategic Goals]:[Strategic Goals], CONTAINS("Self-Service", @cell))

You can do a sumifs for your objective. I just pulled this one since it had sufficient complex rules to help you create a complex rule for your case.

Jennifer C

@Alexander Orsini Thank you. However, I don't want to sum or count the cells, I want it to return the last numerical data showing in the row.

Genevieve P.

Hi @Jennifer C

You can use an INDEX(MATCH to find the MAX date in the Week End Date column and return the value in this current column that matches this.

An INDEX(MATCH works like this:

=INDEX([Column with Value to Return]:[Column with Value to Return], MATCH("Criteria", [Column with criteria]:[Column with criteria], 0))

In your instance, the "Criteria" is the Max Date, so:

MAX([Week End Date]:[Week End Date])

Try this in your Online FBM Unscored column:

=INDEX([Online FBM Unscored]:[Online FBM Unscored], MATCH(MAX([Week End Date]:[Week End Date]), [Week End Date]:[Week End Date], 0))

You'll just need to swap out the column reference at the beginning of the formula if you want to find different values.

Let me know if this works for you!

Cheers,

Genevieve

Jennifer C

@Genevieve P

Hi Genevieve! That somewhat works. The problem is that the dates are prepopulated for the full year, so it will only pull in the data that is on the last day for the year. This sheet is updated each week and if there is data for the column a number is entered or N/A if there is no data for that week. Is there a way to modify it so that I can say only pull from the last row that has numerical (not showing N/A) data? I don't want it to be based on date, but they want to see just the last data recorded regardless of week.

Paul Newcome

Give this one a go...

=INDEX(COLLECT(CHILDREN(), CHILDREN(), ISNUMBER(@cell)), COUNTIFS(CHILDREN(), ISNUMBER(@cell)))

Jennifer C

https://community.smartsheet.com/discussion/comment/299295#Comment_299295

@Paul Newcome It worked perfectly! Thank you soooo much!

Genevieve P.

Paul to the rescue! I'm glad you found a solution. 🙂

Paul Newcome

@Jennifer C Happy to help. 👍️

@Genevieve P Here's a little something you might be interested in that I came across while testing this one...

I originally had a slightly different formula which should have worked, but something told me to go ahead and test it.

=INDEX(COLLECT(CHILDREN(), CHILDREN(), AND(@cell <> "N/A", @cell <> "")), COUNTIFS(CHILDREN(), AND(@cell <> "N/A", @cell <> "")))

When I tested it, I got the #INVALID COLUMN VALUE error (even though it was definitely in a text/number column). After some frustration I had the thought that maybe it was because we had both text and numerical values within the range of CHILDREN(). If that were causing an issue it still shouldn't have been that particular error, but I figured I would just go ahead and try it.

So I did the usual and started to build out a helper column for each of the three to convert all entries into text. I got the first one built and my laziness kicked in. I thought I would try the ISNUMBER function to see if we could isolate the numbers instead of evaluating everything and excluding text and blanks.

And it worked!

Anyway... I thought you might be interested to know that my initial attempt (above) threw a rather odd error message.