A 2-part formula question

Jaime Elizabeth

Good afternoon Community,

I'm working on a new summary sheet and have a 2 part question I need help on…Some background: I have a sheet to track our recruiting efforts and am working on a Summary Sheet I'll use to build a dashboard.

PART 1
I need to count how many reqs are open each month. This would include any reqs that are open from the previous month that has not closed (Date Offer Accepted is blank) as well as those that closed during the month. So far I have the following formula…while I'm expecting it to return 3 for January, it's only returning 1.

PART 2
From here, I need to be able to calculate how much review is being lost month over month. The difference is that we want to give a 1 month grace period for reqs that have been opened before we start the calculation. For example, these 3 reqs opened in January wouldn't begin to accrue until February…even so, since the last req has a closed date of 1/20, it wouldn't accrue anything. So if we were using a base amount of $1,000, in February I'd expect a formula to return $2,000.

I'm honestly not sure where to even start with that formula.

Any help would be greatly appreciated!
~Jaime

Mark.poole

@Jaime Ciabattoni

the first thing I noticed is you have your Numbers wrapped in "". this makes it a text value instead of a numerical value.

thus you are only getting a count of 1 "for the blank value".

=COUNTIFS({Year Opened}, 2024, {Month Opened}, 1, {Month Closed}, OR(@cell >= 1, @cell = ""))

=COUNTIFS({Year Opened}, 2024, {Month Opened}, 1, {Month Closed},OR(@cell >1,@cell =""))*2000

Jaime Elizabeth

@Mark.poole Thank you for responding. The updates to the first formula make sense. However, the 2nd formula doesn't seem to take into consideration the additional need to skip the first month before accruing cost against the req. Any thoughts there?

Mark.poole

@Jaime Ciabattoni

So to be sure. You want it based on when you run the formula?

=COUNTIFS({Year Opened}, 2024, {Month Opened},@cell < MONTH(TODAY())-1,{Month Closed},OR(@cell =MONTH(TODAY())-1,@cell =""))*2000

Jaime Elizabeth

Hi @Mark.poole,

I tried using these formulas more today and have hit a snag.

Starting with Part 1, I realized that for later months in the year, I had to build out the formula like this (using April as an example):
=COUNTIFS({Year Opened}, "2024", {Month Opened}, OR(@cell = 1, @cell = 2, @cell = 3, @cell = 4), {Month Closed}, OR(@cell >= 4, @cell = ""))

Is there a more succinct way to write this formula for each month?

For Part 2, I suspect the same will be true for later months so do want to figure out a short way to update the formula for each month. However, the MONTH(TODAY())-1 piece doesn't seem to be working. This is how I have it for January:
=COUNTIFS({Year Opened}, "2024", {Month Opened}, "1", {Month Closed}, OR(@cell < MONTH(TODAY()) - 1, @cell = "")) * 1000

I'm expecting it to return 0 because all 3 reqs opened in January. However, it's still returning 3000.

Any thoughts?
~Jaime

Mark.poole

@Jaime Ciabattoni

For Part 1 You could create a reference sheet. however again if it is based on the month you are opening it. you could also do <Month(Today()) so it will always return any dates on month open that is not the current month.

for Part 2 It works based off of when you run it. Thus MONTH(Today()) would be this month. the -1 makes it last month. The issue is all three of January closing dates are before May, or blank so all three are being counted.

Jaime Elizabeth

@Mark.poole,

Part 1: I'm not sure I understand this…but let's focus on Part 2 since I have Part 1 working, I just was hoping I could get the formula cleaner.

Part 2: Ah ok, so I don't want the formula to be based on today, instead I want it to be based on the date the req was opened. So, if it was opened on 1/22/24, the formula shouldn't count it until 2/22/24. If the req shows a closed date of 2/21, it should never count it. If it closes on 5/22, it should count for Feb, Mar, and Apr.

Mark.poole

@Jaime Ciabattoni

I have tried several things for you to get Part two to work. how ever the formula was doing funny things using Month() and Day() when putting it in the formula itself. how ever if I separated them into helper columns and then created another column that would flag it with a 1 I came up with this.

=IF(OR(AND([Month Opened]@row + 1 = [Month Closed]@row, [Day Opened]@row > [Day Closed]@row), [Month Opened]@row = [Month Closed]@row), "", IF(AND([Date opened]@row <> "", [Day Closed]@row = ""), 1, IF([Date opened]@row < [Date offer Accepted]@row, 1, "")))

This formula is in the helper column of the screen shot.

Jaime Elizabeth

@Mark.poole Wow. Thank you so much for taking the extra effort to figure this out and put together the information!!! I'll see if I can replicate it!

Jaime Elizabeth

@Mark.poole

Hoping you may still be able to help me around these couple of formulas. After doing some further digging and testing I ended up changing my Part 1 formula to use the DATE function which seems to have cleaned things up significantly and has gotten me almost accurate numbers. In this version, I just put the dates I'm searching between for each month's formula. See below.

Where I am still stuck is that I have some Reqs that have been canceled. Let's use the following as an example: A req was opened on 7/30/24 and canceled on 10/15/24. If a req is canceled, a Status column will say "Canceled" and the Offer Accepted column will say "n/a".

When I add this as a test in my sheet, I'm only seeing it picked up in the Opened formula so I need to figure out what needs to change in my Rolled Over formula. I'm expecting for August and September to see it counted. It should NOT be counted from October onward.

I'm assuming this should be a version of an OR or AND statement but writing that syntax is still not something I understand. If I think through it using just words and focusing on August when it should first be counted…Count the req if the Date Opened is before 8/1 and the Offer Accepted date is after 8/31 OR Date Canceled is after 8/31. I tried the following but got an INVALID OPERATION error:

=COUNTIFS({Date Opened}, <DATE(2024, 8, 1), OR({Offer Accepted} > DATE(2024, 8, 31), {Date Canceled} > DATE(2024, 8, 31)))

Thank you in advance for any help.
~Jaime

PS There's still the fun around Part 2 but I also got more requirements on it so am trying to figure that out this AM before asking for more help.

Mark.poole

@Jaime Ciabattoni

IT looks like you are on the right track. first lets fix the syntax of your formula. AND and OR functions go in the front of the formulas ex . =IF(OR( "criteria","criteria"),true,false) or =IF(AND( "criteria","criteria"),true,false)

how ever the syntax for a countifs does not work very well with AND and OR statements. The reason being is that both are considered criteria and not range. Thus to make it work you have to do

=countifs(Range, OR())

countifs also has an implied AND function in it.

Thus the best way to do it is

=COUNTIFS({Date Opened}, <DATE(2024, 8, 1),{Offer Accepted}, > DATE(2024, 8, 31))+COUNTIFS({Date Opened}, <DATE(2024, 8, 1), {Date Canceled} > DATE(2024, 8, 31))

Two counts added together one for when the offer is accepted and one for if the date gets canceled.

I hope this helps explain it a bit better for you.

Jaime Elizabeth

@Mark.poole

THANK YOU for the additional explanation. That is definitely helpful! When I put into SS I do see the formula working but it's not returning the numbers expected. Here's a couple of screenshots of the formulas and the data…

So close! Let me know if you need anything else to help. Maybe I missed explaining something that would pull in the previous month's data.
~Jaime