Formual for summing/counting

Matthew 5

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...

Eric Law

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

Matthew 5

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.

Michael Drayton

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!

Matthew 5

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.

Michael Drayton

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

Matthew 5

@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.

Michael Drayton

@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?

Matthew 5

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

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

@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

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

@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

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