Forum Discussion
Countif
I'm running into issues when trying to countif. I have one column that has dates that I would like to count going back 90 days from today. I've seen examples using countif with specific dates but none with a dynamic content(today-90)
I've attached a pic of the report layout. One issue with this layout is the fact that I need to group by price range as listed in the first column "Price range" When I say problem I'm required to do a countif for every price range from 50-150, 151-200, 201-250(you get the idea)
I'm also open to any suggestions for a better/easier readout of the data.
Thank you in advance.
3 Replies
For starting it is better to keep the upper limit of the price range on a seperate column. Like an additional column on the right of price range column to hold the upper limit like 199.999, 224.999... You can hide it if you do not want it to be seen. You can automatically create upper and lower limits as text depending on this column values however this is another issue.
Then you can use the TODAY()-90 function to get the date 90 days before today, this will update whenever you reopen the file or the page recalculates so to keep things simple and fast I advise you use this in a seperate cell and reference this cell address instead.
To find whatever you need you can use COUNTIFS instead. I cannot see the date coumn in your picture. so I cannot help with the actual formula. But if your dates are on Column E and your newly created upper limit coulumn in B and your date criteria is in B2 and you have 500 rows of data you can use:
=COUNTIFS($E$1:$E$500, ">="&B2 ,$B$1:$B$500, "150")
150 here in the formula is the upper limit
Thank you for the reply. Separating the values between two columns works great. I've provided the spreadsheet as well as a more detailed explanation. As I am still a bit lost with syntax.
In cell E5 of the Reports page I want to count the number of Active listings with a list price >=A5 and <=B5
The cell reference would be:
Data page, column R("Status") =A
Data page, column X(List Price)
Hopefully this makes a bit more sense.
Thank you
- Is it possible to share the file?
