COUNTIF leads added last week

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.

Find more posts tagged with

Sales

Formulas

Accepted answers

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)

All comments

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.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.