COUNTIF leads added last week

Yahaira Paredes

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?

Thank you very much in advance for your help.

Paul Newcome

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)

Paul Newcome

Try this instead:

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

Yahaira Paredes

https://community.smartsheet.com/discussion/comment/349482#Comment_349482

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!

Paul Newcome

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)

Yahaira Paredes

Hi @Paul Newcome!

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!

Paul Newcome

Happy to help. 👍️

Yahaira Paredes

https://community.smartsheet.com/discussion/comment/349773#Comment_349773

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

Paul Newcome

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

Yahaira Paredes

https://community.smartsheet.com/discussion/comment/349950#Comment_349950

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!