Formual for summing/counting

Options
Matthew 5
Matthew 5 ✭✭
edited 09/15/23 in Formulas and Functions

I have a running sheet that tracks the date for a review. The basic sheet has Location, District, ReviewDate, Available_to_Review.

I need the column to be a calculated field that give me a Y/N or 1/0) for every record. The criteria would be if a Location has had a Review Date in the last 30 days, the Available_to_Review column would show N for every location in that district.

Ideally, it then updates the table like this because the last Review date 9/10 sets the Available for Review column for all Districts to 0.


This would allow me to create a Report that only shows Locations in Districts that have not had a review date within the last 30 days.

Currently my formula looks like this...

=(IF([Review Date]@row > TODAY(-30), COUNTIF(District:District, District@row), 0))

And returns

I get why this is giving me the results I have, but I am stumped on how to get the results I want...

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Options

    @Matthew 5 And what is the result that you want?

  • Matthew 5
    Options

    Ideally, if any listed Review Date = today(-30) the Available for Review Column would then show all 0 for not avaliable or 1 for available.


  • Alpha Chucky
    Alpha Chucky ✭✭✭✭
    Options

    I tried your formula and i could not repeat the error. It worked every time. It might be how you inputted the date or your formula in the "Available For Review" column didn't carry the formula down.


    Good luck!

    Projects Delivered. Data Defended.

  • Matthew 5
    Options

    Thanks @Alpha Chucky,

    Yes, that is the result I got as well, but now I need to find a way to the the Districts that are are a "0" value to populate with a number as well.


    This would allow me to have a report that I could filter out all locations in a district that has had a review. So my report would not show any Austin locations so that we would know not to conduct a review.

  • Alpha Chucky
    Alpha Chucky ✭✭✭✭
    Options

    @Matthew 5 can you give me an example / hypothetical situation, please?

    Projects Delivered. Data Defended.

  • Matthew 5
    Options

    @Alpha Chucky

    So the first sheet I have is this. it tracks the date the for a review for a location and district.

    I have another sheet that has all locations, with their Districts and a list of metrics. This sheet gets updated with new metrics every week and overwrites the last weeks data, and looks like this...


    What I want is a report that shows this second list, but filters out any District that has had a review in the last 30 days based on the first list...

    I thought if that I had a column that could calculate either a YES/NO value or 1/0 value for each Row on the first list that would return that if ANY of the same Districts had a review in the last 30 days. The first table would then look something like this.

    Then I could use Vlookup on the second sheet that would allow me to filter out the Districts. The sheet then would look like this.


  • Alpha Chucky
    Alpha Chucky ✭✭✭✭
    Options

    @Matthew 5 I'm going to test this out, but as I do, question: Would your purpose be solved if you created a report filtering only those that have not received a review in less than 30 days?

    Projects Delivered. Data Defended.

  • Matthew 5
    Options

    Unfortunatly I need to filter out all Districts from my list if even one of the locations in that district has had a review. Playing with Excel I came up with this.... using the formula in the AVR column

    =IF(COUNTIF($B$2:$B$15,B2)>1,"1","0")

    That would at least get me each row with a 1/0 that I can use as a filter


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try this in the second sheet:

    =IF(COUNTIFS({Source Sheet Date Column}, @cell>= TODAY(-30), {Source Sheet District Column}, @cell = District@row)> 0, 1)

  • Matthew 5
    Options

    @Paul Newcome

    Getting closer here. This put on the correct path. I don't think there is a way to do this without multiple columns...my solution was to add one more column a "helper Column" which sums. It takes your column results and then Sums them by Districts.

    =SUMIF(District:District, District@row, [Limit Districts]:[Limit Districts])


    Anything greater than 0 is a District that has had a review in the last 30 days. That can be used now to filter out the District I don't want people to see. It seems clunky and I am going to give it a whirl to see how well it works and if it breaks anywhere... if a cleaner solution comes up I will edit this.

    Thanks everyone for the help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Even just using my formula, anything that isn't blank is on that hasn't had a review within the past 30 days.


    If you want the total count, you can just get rid of the IF portion and use the COUNTIFS as a standalone.

  • Matthew 5
    Options

    @Paul Newcome

    You are correct. I was looking at it differently...


    It works, fine, one thing I noticed after putting it in, is that evidently there a value of "Pending" in some of the rows for the Date...I tried adding in an OR statement to account for this but I am getting errors. Where did I good up?

    =IF(COUNTIFS([Post-Week Ending]:[Post-Week Ending],OR(@cell >= TODAY(-40), @cell="Pending" District:District, @cell = District@row) > 0, 1)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You need to take one of the closing parenthesis from the very end and put it after "Pending" to close out that OR function.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!