Counting from two different date columns without counting twice

Joyce Ghali

I have a report that is pulling data on traffic in the past week. I have two date columns, one is First Visit, and the second is Most Recent Visit. The report is pulling the rows based on whether one of those columns has a date between last Monday and this Monday. Im trying to make formula that does the same

I also have a name column to check who is making the entry

=COUNTIFS({Name}, "John Doe", {First Visit}, (IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())), {Name}, "John Doe", {Most Recent Visit}, (IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())))

This formula is only pulling the rows that have a date in the Most Recent Visit column, but not the First Visit.

What part of the formula is stopping some data to be be pulled?

Genevieve P.

Hi @Joyce Ghali

Since you want to see the COUNT of values if it's either First Visit OR the Most Recent visit, you'll actually want to add two separate COUNTIFS statements together.

Right now you have the criteria listed side-by-side in the same function, so it will only return a count if both values are true (where both appear in the same row).

Try this:

=COUNTIFS({Name}, "John Doe", {First Visit}, (IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))) + COUNTIFS({Name}, "John Doe", {Most Recent Visit}, (IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())))

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

To follow-up, I just re-read the title of your post. It sounds like you don't want duplicates, so you only want to count a row where both instances may be true ONCE, is that correct?

So we first count each separate instance, then we can minus off 1 count for each row where both criteria are met:

=(COUNT of First Visit) + (COUNT of Most Recent Visit) - (COUNT where one row has both)

There may be a simpler way of doing this, but this formula should work for you:

=COUNTIFS({Name}, "John Doe", {First Visit}, (IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))) + COUNTIFS({Name}, "John Doe", {Most Recent Visit}, (IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))) - COUNTIFS({Name}, "John Doe", {First Visit}, (IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())), {Most Recent Visit}, (IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())))

Let me know if I've misunderstood and I'm happy to help further!

Cheers,

Genevieve

Joyce Ghali

It worked! Thank you so much for your help

Genevieve P.

No problem! 🙂