I'm needing assistance with a formula, and hope someone can help. I did a lot of "googling" and the formula string I have isn't bringing back the results I was expecting, so I'm not sure what is wrong with my formula.
Explanation:
I have a spreadsheet that has "open date", "closed date", and "date last attempted". "date last attempted are those rows that aren't truly "Closed" deals, but because the customer didn't call back we aren't keeping it open, but also don't want credit as being closed.
I have monthly score card that is completed the next month for prior month's work and i'm trying to read all the data off of a tab in order to pre-populate the score card. I need to show how many "Open date" items were in or prior to the month I'm scoring (Let's use January as an example) that are considered "Open". The only way to tell if the case is Open is to check both the Closed Date column and the "last attempted" column for a date. If there is no date in either, it is still open. If there is a date in either (will never have a date in both on the same row), it needs to be counted only when the date is GREATER than the last day of the scored month (to not include thing that were closed/last attempted in current month). Meaning, I don't want the formula changing the results as future dates are populated. Trying to keep the formula showing a "point in time", which should be what is "open" as of the very last day of the month.
This is the formula I was attempting to use, but it doesn't seem to be reading it right. Help please!
=COUNTIFS(Muck!$B:$B,"<2/1/2017",Muck!$K:$K,{"",">1/31/2017"},Muck!$N:$N,{"",">1/31/2017"})
B is the open date, K is closed date, and N is the last attempted date.
Thanks in advance!!
Bookmarks