Hello,
First time poster. I am using EXECL 2007 and have one column for event description (some are duplicated, some are blank) and another column for start date. I am building an event summary at the top of the worksheet and would like to count the unique events & blanks in the event column prior to Today's date. I have tried several differnt iterations of IF, COUNT(A),COUNT(IF), and SUMPRODUCT, but I can't seem to put it all together for one solution.
Thanks.
Last edited by VBSK8R; 11-21-2011 at 12:25 PM.
Perhaps something like:
=COUNT(1/FREQUENCY(IF(B1:B100<TODAY(),MATCH(A1:A100&" ",A1:A100&" ",0)),ROW(A1:A100)-ROW(A1)+1))
adjust ranges to suit... and confirm with CTRL+SHIFT+ENTER not just ENTER
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
It comes up with zero:
=COUNT(1/FREQUENCY(IF(I11:I20000<TODAY(),MATCH(H11:H200000&" ",I11:I2000&" ",0)),ROW(I11:I20000)-ROW(I11)+1))
Column I is the designation (some events have the same designation, some are blank, want to count each unique designation and every blank), Column H is the start date and want to count each instance of Column I prior to today's date.
Should it be?
=COUNT(1/FREQUENCY(IF(H11:H20000<TODAY(),MATCH(I11:I20000&" ",I11:I20000&" ",0)),ROW(I11:I20000)-ROW(I11)+1))
and confirm with CTRL+SHIFT+ENTER keys to get { } brackets around the formula.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
It is now coming up with seven(7): here is the list of data:
Designation Start Date
11-01 18-Oct-10
11-01 18-Oct-10
11-01 18-Oct-10
11-01 18-Oct-10
11-02 29-Nov-10
11-02 29-Nov-10
11-02 29-Nov-10
14-Feb-11
11-03 4-Apr-11
11-04 16-May-11
11-04 16-May-11
11-04 16-May-11
11-04 16-May-11
18-Jul-11
11-05 18-Jul-11
11-06 23-Aug-11
11-06 23-Aug-11
11-06 23-Aug-11
19-Sep-11
11-07 19-Sep-11
11-07 19-Sep-11
17-Oct-11
12-01 31-Oct-11
12-01 31-Oct-11
12-01 31-Oct-11
12-01 31-Oct-11
12-02 28-Nov-11
Based on the uniques and blanks on Designation, it should read twelve(12).
Okay assuming that list is in A2:B28, try:
=COUNT(1/FREQUENCY(IF(B2:B28<TODAY(),MATCH(A2:A28&B2:B28,A2:A28&B2:B28,0)),ROW(A2:A28)-ROW(A2)+1))
CSE confirmed.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks!!!
Marked as solved and bumped your rep, thanks again!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks