Counting from two different date columns without counting twice
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?
Answers
-
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
-
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
-
It worked! Thank you so much for your help
-
No problem! 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!