A 2-part formula question

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

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

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

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

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.

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

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.

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

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

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.