Countif in last 3 months

Options

Hi, I need to count if certain data is used in 6 different columns. Here is what I have so far:

=COUNTIF({Test Range 1}, [Name]@row) + COUNTIF({Test Range 2}, [Name]@row) + COUNTIF({Test Range 3}, [Name]@row) + COUNTIF({Test Range 4}, [Name]@row) + COUNTIF({Test Range 5}, [Name]@row) + COUNTIF({Test Range 6}, [Name]@row)

The above formula works but I need to somehow combine it with the below formula to calculate if any of the 'Test Range' data was inputted in the last 3 months:

=COUNTIFS({Test Date Range 1}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 3), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 3))

Apologies if this is really simple but I've been trying to get this to work for days.

Below is a screenshot of the sample data I have:

Thank you for any help you can provide,

Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Lewis Irving

    Let's see if this will work for you

    =COUNTIFS({Test Range 1}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 2}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 3}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 4}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 5}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 6}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY())))

    Since you want to add dates, you must also add the range the dates come from

    Will this work?
    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!