• Status: Solved
• Priority: Medium
• Security: Public
• Views: 295

# formula to exclude dates outside current year

Hello Experts
I am trying to create a formula that counts the quantity of "Y"s in column A, subracting (or discounting) from that total, the count of rows with dates in column B earlier than 1/1/current year) AND subtracting dates after 12/31/current year).    I have attached the file with formulas I have tried with no joy.
Berry
formula-excludes-dates-includes-.xlsx
0
Berry Metzger
• 3
• 2
1 Solution

Commented:
Berry,
Try a COUNTIFS formula like:
=COUNTIFS(tblHolidays[Use?],"Y",tblHolidays[Date],">=" & DATE(YEAR(TODAY()),1,1),tblHolidays[Date],"<=" & DATE(YEAR(TODAY()),12,31))

0

Commented:
Or a SUMPRODUCT:
=SUMPRODUCT((tblHolidays[Use?]="Y")*(tblHolidays[Date]>=DATE(YEAR(TODAY()),1,1))*(tblHolidays[Date]<=DATE(YEAR(TODAY()),12,31)))

The attached file shows both the SUMPRODUCT and COUNTIFS formulas.
formula-excludes-datesQ28271337.xlsx
0

Lean process improvement consultantAuthor Commented:
You nailed it Brad!
Now I have two formulas, not just one, to add to my knowledge base.
I chose to use the SUMPRODUCT formula as I understand it has less overhead than COUNTIFS
Thanks a lot
Berry
0

Commented:
Berry,
For what it's worth, the converse is true: COUNTIFS and SUMIFS impose less calculation burden than SUMPRODUCT.

0

Lean process improvement consultantAuthor Commented:
Thanks for that Brad.  I was assuming all "IF" formulas were volatile, and because INDEX is not for instance and is faster than multiple IF()s, that any formula with "IF" had more burden.  Good to know.
Thanks much again,
Berry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.