With below formula I am counting unique values. Everything was working well until I added the dates. Any suggestions to solve this?
Formula:Please Login or Register to view this content.
Thanks
Thomas
With below formula I am counting unique values. Everything was working well until I added the dates. Any suggestions to solve this?
Formula:Please Login or Register to view this content.
Thanks
Thomas
Last edited by thomasuponor; 02-19-2021 at 07:34 AM. Reason: Sample file attached + Title changed
Administrative Note:
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Your title does not mention dates at all!!!
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Please add a sample sheet, too (yellow banner, top of page). It makes it easier if we can SEE what we're dealing with...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi both, thanks for letting me know
Sample file attached and title is now changed.
Hope everything is OK now.
This is HUGELY slow. Do you REALLY Have 1,000,000 rows????
Since you have O365, you can use a combination of dynamic arrays:
=COUNTA(UNIQUE(FILTER(F2:F100,(D2:D100>=DATE(2020,1,1))*(D2:D100<=DATE(2020,12,31))*(ISNUMBER(SEARCH("Visit",I2:I100)))*(J2:J100="Ole Olsen")*(N2:N100="Installer/Contractor"),"")))
I am completely confused about what you THINK this bit is doing:
(ISNUMBER(SEARCH("|"&visits!I:I&"|";"|Visit|Online Meeting|Webinar|Join Visit|")))
in your original formula. Please explain!!
Hi Glenn,
Sorry for bringing misinformation. My version is 2016 (is corrected now).
About the bit: This part of the formula is counting "Visit", "Online Meeting", "Webinar" OR "Join Visit".
In the sample file all values are "Visit". But it could easily be one of the other.
Hope it makes sense.
OK. Back to the drawing board. BUT NEVER use whole column references in array formulae. That formula takes SECONDS to calculate. How many rows (maximum) do you REALLY have?
Oh yeah. You are very right. I only have 10000 rows.
OK. I have set it here for 10000 rows. It is still slow... but not as slow... it does have more work to do, though...
=SUM(INDEX((D2:D10000>=DATE(2020,1,1))*(D2:D10000<=DATE(2020,12,31))*(F2:F10000<>"")*(J2:J10000="Ole Olsen")*(ISNUMBER(SEARCH({"¦Visit¦","¦Online Meeting¦","¦Webinar¦","¦Join Visit¦"},"¦"&I2:I10000&"¦")))/COUNTIFS(I2:I10000,I2:I10000&"",J2:J10000,J2:J10000&"",N2:N10000,N2:N10000&"",F2:F10000,F2:F10000&""),0))
Glenn
Check to see if it has the array formulae {} round it, on receipt. I am not sure, but the addition of the pipe round column I (to prevent double counting of Visit and Join Visit) may mean that you will have to enter it as an array in your version of Excel.
Looks very good. I just tried on the sample file and the result is 1.
I have replaced , with ; and tried with and without {}.
Result should be 9.
Also noted the part of formula is missing: N2:N1000="Installer/Contractor"
Thomas
What did you see when you opened MY file, with the formula in place (yellow cell)?
Hold on. I just checked your sample file and you have used "\". I have added ="Installer/Contractor" and it's working perfectly now.
Formula looks like this:
Formula:Please Login or Register to view this content.
So... when you opened it, the formula array (the bit inside the {} in the middle of the formula) appeared as "\" it left here as a comma!! I thought it MIGHT have turned into a semicolon... but wasn't expecting that!!
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
Hold on again
I tested on bigger amount of data and the result turn out as decimal number (should be impossible)
Let me attached in 2 sec.
Bigger sample attached.
Oh dear... is it really more than an hour since I opened this!!??
Try it now. Too big to check manually... but it is returning an integer!!
I managed to make below formula work. It's very similar to the initial and the result is correct. The only difference is the part with the date.
Formula:Please Login or Register to view this content.
I see that you are using whole column references in your formula again.
ohhh. thought you wouldn't notice. lol
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks