• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Want unique counts of particular value based on date range criteria.

K

kishor

New Member
I want to gather data of unique counts of a particular value based on the date range criteria saved in another column.


Col A Col B

A 1 Jan 2013

B 1 Jan 2013

A 2 Jan 2013

A 3 Jan 2013

A 4 Jan 2013

B 1 Jan 2013


Need unique counts of value "A" of col A based on date range criteria of Col B

For Example: Need unique counts of value "A" from 1 Jan 2013 to 3 Jan 2013

Thus in above the answer is "3" but i want to derive to it by formula and not manually counting


Note: i maintain date format in MM/DD/YYYY format that further converts and displays as DD-MM, above i am trying to be more illustrative thus if you find date format is also crucial in formula pls provide right formula.
 
Hello Kishor,


Try this formula. D2 an D3 are the start and end dates.


=SUMPRODUCT(($A$2:$A$7="A")*($B$2:$B$7>=$D$2)*($B$2:$B$7<=$D$3))


Regards,

Prazad
 
Thanks prasad, date is mentioned in Col B only, i want to take unique counts based on date range.

For Example want counts for a Week, Month or desired range.
 
Hello Kishor,


You can change the desired date range in that formula, be it a week or month or year. Could you pls upload a sample so that I can explain you better?


Prazad
 
Thanks for sending the sample.


Pls download and see if this is what you require.


http://www.2shared.com/file/TWylhTxr/Sumproduct.html


Prazad
 
Thanks prazad, you have hit the bluzzeye!!!

Instead of specifying value from the sheet by selecting a particular cell, can i write it:- i.e. mail, 2013, 1, 1 and 2013, 1, 5
 
can i enter the desired value in the formula instead of selecting it from the data range?


=SUMPRODUCT(($B$4:$B$102= --HERE--> MAIL ---- )*($C$4:$C$102>= HERE> 2012, 1, 1)*($C$4:$C$102<= HERE> 2012, 1, 3))
 
Hi, kishor!

Yes, you can do that. But you should edit and modify your formula each time you change your criteria. I think that prazad82's E8 and E9 parameters are fine, and I'd also add the starting and ending dates at E10:E11 range (or anywhere else suitable for you).

Regards!
 
You must log in or register to reply here.
Back
Top