Options

Good day!

I'm working on this new KPI report for leads added last week, and I'm using this formula:

=(COUNTIF({Aldo Aguilar - Pipeline Range 1}, "2 - Upselling", "4 - Lead Generation", COUNTIF({Aldo Aguilar - Pipeline Range 2}, WEEKNUMBER(TODAY(-1)))))

But at the moment that I hit enter, it returns "#INCORRECT ARGUMENT". Is there something I'm missing on my formula?

Tags:

• ✭✭✭✭✭✭
Options

My apologies. I saw the syntax issue and missed what we were actually trying to accomplish.

Try moving the -1 portion to outside of the WEEKNUMBER portion. By having WEEKNUMBER(TODAY(-1)) you are basically pulling the week number for yesterday. WEEKNUMBER(TODAY()) - 1 will pull the week number for today and then subtract 1 from that.

=COUNTIFS({Aldo Aguilar - Pipeline Range 1}, OR(@cell = "2 - Upselling", @cell = "4 - Lead Generation"), {Aldo Aguilar - Pipeline Range 2}, WEEKNUMBER(TODAY()) - 1)

• ✭✭✭✭✭✭
Options

=COUNTIFS({Aldo Aguilar - Pipeline Range 1}, OR(@cell = "2 - Upselling", @cell = "4 - Lead Generation"), {Aldo Aguilar - Pipeline Range 2}, WEEKNUMBER(TODAY(-1)))

• Options

Hi Paul!

Thank you for your answer. The error "#incorrect argument" doesn't show anymore, it gives me a zero count.

There's 9 from last week, could it be something else that I'm missing?

Thank you very much in advance!

• ✭✭✭✭✭✭
Options

My apologies. I saw the syntax issue and missed what we were actually trying to accomplish.

Try moving the -1 portion to outside of the WEEKNUMBER portion. By having WEEKNUMBER(TODAY(-1)) you are basically pulling the week number for yesterday. WEEKNUMBER(TODAY()) - 1 will pull the week number for today and then subtract 1 from that.

=COUNTIFS({Aldo Aguilar - Pipeline Range 1}, OR(@cell = "2 - Upselling", @cell = "4 - Lead Generation"), {Aldo Aguilar - Pipeline Range 2}, WEEKNUMBER(TODAY()) - 1)

• edited 11/03/22
Options

It worked! Thank you very much.

EDIT

Well, now it gives me a number, but it's not the correct number... it gives me 16 and the correct number is 9.

=COUNTIFS({Aldo Aguilar - Pipeline Range 1}, OR(@cell = "2 - Upselling", @cell = "4 - Lead Generation"), {Aldo Aguilar - Pipeline Range 2}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)

I don't know what I am doing wrong!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

Sorry Paul :( I added another issue to that... Maybe it's something on my database.

• ✭✭✭✭✭✭
Options

I see you are using a filter. What are your filter details?

• Options

It was the filter using the dates of last week. I tried with another databases with your formula and those worked perfectly, so it's the data on that particular one. :) thank you very much for the follow up!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!