Hello, please see my attachment. in cell E2 (Yellow) I want to count the number of unique times in column B where the date in column A is today.
As you can see I have typed this in as 36, but would like this doe by formula - ideally not sumproduct or something that is quite a heavy calculation formula.
Thanks in advance
deleted, now after rereading it a second time I see what you want. working on that one now.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Ok thank you
I got you there but mine took two helper columns. I did countif(s) in C and D then a countifs in F2.
this in C =IF(COUNTIF($B$2:B2,B2)>1,"",1) autofilled down
this in D =IF(COUNTIF($A$2:A2,A2)>1,"",1) autofilled down
this for the count total for both today and unique times... =COUNTIFS(A:A,TODAY(),C:C,1)
If I think longer on it maybe I can figure out a way to consolidate all.
Thanks for the reply, Ideally want to avoid helper columns aswell though and run it through one formula
Try array entering this formula.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
=SUM(IF(FREQUENCY(IF($A$2:$A$765=TODAY(),$B$2:$B$765),$B$2:$B$765),1))
Dave
Hello, thank you - I'd already worked out a way using array but your formula is tidier.
Thank you
Last edited by AliGW; 02-10-2020 at 07:47 AM. Reason: Please don't quote unnecessarily!
Care to share?Originally Posted by cmb80
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks