Can you use countif +1?

Options

Hi!

I am creating a results report that is updated weekly. I have a ton of variables that we're monitoring based off the week (i.e. week 1, week 2, etc..

I am wondering if I can save time with the weekly updates by using a COUNTIF formula but always have it increase by 1.

By this I mean:

=COUNTIF(reference sheet week column "1") for the week 1 total. I'd love to be able to add some type of +1 functionality so I could apply this formula to the whole row and and the COUNTIF reference would automatically count the next number (i.e. week 2, week 3, etc..)

I hope this makes sense!!

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Christa,

    Too Easy. Try:

    =COUNTIFS({PLSC Report Full File Range 3}, [weeks]@row, {PLSC Report Full File Range 2}, "Auto", {PLSC Report Full File Range 1}, HAS(@cell, "Account Discount"))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Christa Brown ,

    There are a few ways to do this. Which is best will depend on how complicated your sheet is. If you are counting or summing by week, then using the WEEKNUMBER() function will do it. Weeknumber determines the week of the year, from 1-52 or 53, for a date. Weeknumber(today()) is this week. Weeknumber(today())+1 is next week, -1 last week.

    It would be helpful to see your sheet structure, in a screenshot, and have more detail about what you want to do. Can you provide more?

    Happy to help more.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Christa Brown
    Options

    Thank you for the response - especially on a weekend! The WEEK function is great to know about! However, I don't think it'll work in this case.

    Specifically, I am tracking the # of weeks a program has been running with the start week being 1. I am gathering results by week using the week # in my formulas.

    So one that is being used a lot right now is: =COUNTIFS({WeekColumn}, "1").

    I am wondering if there is a way to adjust the formula to automatically increase the 1 to 2, 3, 4, and so on so I can convert the formulas to column formulas and not have to update the week #s with new week results.

    Thank you!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Christa,

    I'm not able to visualize what you're trying to do. Can you attach a screenshot? I'm confideny you can use a formula to automate your updates and avoid manual formula changes each week. To help I need to understand your sheets and reports.

    If the start week of every program is 1 then your formula, =COUNTIFS({WeekColumn}, "1"), is counting those programs in their 1st week. When you change the formula to =COUNTIFS({WeekColumn}, "2") you're now counting programs in their 2nd week, but you're no longer counting programs in their 1st week? Am I understanding this much?

    Your =COUNTIFS({WeekColumn}, "1") formula can be a column formula now. It meets the requirements.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Christa Brown
    Options

    HI! You are correct in your understanding. I want to isolate each week's result's. Here's what one of the simpler tables looks like:

    The formula I am using to get the Errors and the Weekly counts is:

    =COUNTIFS({PLSC Report Full File Range 3}, "1", {PLSC Report Full File Range 2}, "Auto", {PLSC Report Full File Range 1}, HAS(@cell, "Account Discount"))

    What I'm hoping is that I can automatically increase the week from 1 to 2 to 3, etc.. that the COUNTIF formula looks for vs. manually updating the week from "1" to "2" etc... (the bolded text) in the formula).

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Christa,

    Too Easy. Try:

    =COUNTIFS({PLSC Report Full File Range 3}, [weeks]@row, {PLSC Report Full File Range 2}, "Auto", {PLSC Report Full File Range 1}, HAS(@cell, "Account Discount"))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Christa Brown
    Options

    That DID IT!! This will save so much time in weekly updates!! There are 50+ish variables that I was not looking forward to updating!


    Thank you so much!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Happy to help. Appreciate you contributing to the Community.

    Be Well, Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!