Pulling Year to Date from an entire year's worth of data with a formula?

[KRE]

Below in green is just a small section of my dataset. The formula in F15 pulling the answer of 24 pulls in everything for 2024. I need to pull in everything year to date prior year. I'd assume I'd want to pull in data from column AS instead of AU. and instead of referencing F$3 which is 2024 I referenced something like <=20242 for week 2 of 2024? But I also need to not include anything from 2023, 2022, 2021. My current formula is:

=COUNTIFS('Bid Log New'!$AU:$AU,F$3,'Bid Log New'!$J:$J,"yes, won job")

 

[Fluff]

How about

=COUNTIFS('Bid Log New'!$AU:$AU,F$3-1,'Bid Log New'!$AQ:$AQ,"<="&MONTH(TODAY()),'Bid Log New'!$J:$J,"yes, won job")

 

[KRE]

How about

=COUNTIFS('Bid Log New'!$AU:$AU,F$3-1,'Bid Log New'!$AQ:$AQ,"<="&MONTH(TODAY()),'Bid Log New'!$J:$J,"yes, won job")

How about

=COUNTIFS('Bid Log New'!$AU:$AU,F$3-1,'Bid Log New'!$AQ:$AQ,"<="&MONTH(TODAY()),'Bid Log New'!$J:$J,"yes, won job")

So the "<="&MONTH(TODAY()), That did work for my original question. Follow up question is if I'm running an analysis and I want to know what my count is through the second week of june 2024 or the 3rd week of August 2024, "<="&MONTH(TODAY()) won't work obviously. Thoughts on that?

 

[Fluff]

You can use

=COUNTIFS('Bid Log New'!$AU:$AU,F$3-1,'Bid Log New'!$A:$A,"<="&DATE(f$3-1,4,10),'Bid Log New'!$J:$J,"yes, won job")

Just change the month & day inside the Date function to suit.

 

[KRE]

You can use

=COUNTIFS('Bid Log New'!$AU:$AU,F$3-1,'Bid Log New'!$A:$A,"<="&DATE(f$3-1,4,10),'Bid Log New'!$J:$J,"yes, won job")

Just change the month & day inside the Date function to suit.

That didn't work. It's not a month and day it's a year and week. So 20241 is "year" 2024 and Week 1. and I don't want to type this formula or change this formula multiple times I want to change the reference point. So cell F2 I want to be able to type 202448 and it be the 48th week of 2024 and it'll grab that data from the table.

 

[Alex Blakenburg]

Assuming you still want YTD does this do what you are after:

=COUNTIFS('Bid Log New'!$B:$B,"<="&RIGHT(F$2,LEN($F$2)-4),'Bid Log New'!$AR:$AR,"="&LEFT(F$2,4),'Bid Log New'!$J:$J,"yes, won job")