Can you use countif +1?

Christa Brown

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

Mark Cronk

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

Mark Cronk

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

Christa Brown

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

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

Christa Brown

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

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

Christa Brown

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

Happy to help. Appreciate you contributing to the Community.

Be Well, Mark