Count unique occurrences considering a given parameter.

[Elephant]

i ve got a problem i cant solve. I've got the intuition that i coul solve it using the knowledge i already have, but i cant get to the algorithm to solve it using excel.

Here is the problem.

I' ve got a spreadsheet like the following:

[pre]

Start date and time | Stop date and time | Id
-------------------------------------------------------
01/07/2012 08:45    | 01/07/2012 10:16   | Company1
--------------------+--------------------+-------------
01/07/2012 09:32    | 01/07/2012 11:07   | Company1
-------------------------------------------------------
01/07/2012 07:30    | 01/07/2012 08:30   | Company2
-------------------------------------------------------
03/08/2012 06:15    | 03/08/2012 07:16   | Company1
-------------------------------------------------------
07/08/2012 2352     | 08/08/2012 01:05   | Company1
-------------------------------------------------------

[/pre]
I know the months since a given start date 01/10/2010. I need to know how many months has a given company (Ex: Company1) send the corresponding information.

For the case shown above the results would be: Company1 = 2; Company2 = 1.

 

[Luke M]

If you use the Google search in top-right of this forum, you'll find there are several posts about finding the unique count of something with a specific criteria. I'd recommend starting here:

http://chandoo.org/forums/topic/combine-countifs-and-unique-function


Also, I'm having trouble interpreting your example. To clarify, do your dates use mm/dd/yyyy or dd/mm/yyyy format?

 

[Elephant]

Thanx for answering so diligently. I'll have a look at the link you posted.

The date format is dd/mm/yyyy hh:mm.

Ah1 and I forgot to post that the answer should feet in Excel 2003!

I'll look at the link and turn back to give some fedback.

 

[Debraj]

Hi Formula1Racer...

Can someone help me to remove this Helper Column.. from the attached..


Hi Forensic Department

Can someone please elaborate my Formula to me.. from the attached..

{=INDEX(MONTH($B$2:$B$6)&$C$2:$C$6, MATCH(0, COUNTIF($F$1:F1,MONTH($B$2:$B$6)&$C$2:$C$6), 0))}

Hi Elephant..

What if start Month & Stop Month are not Same?

So I am assuming Month of both fields are always Same..

Let us know if both are different..

For the time Being please find your answer in the attached..


https://www.dropbox.com/s/57mnxl9zv81sn5q/Count%20Unique%20Occurance.xlsx

 

[Elephant]

Luke M:

Thankx, the link gave me the answer i was looking for.

By the way should I rename the post like "Solved" or Something? Sorry for my lack of experience posting in the blog.


Debray Roy:

Sorry, trying to get the answer from a 2003 Office, so can't open your .xlsx file.

 

[Debraj]

https://www.dropbox.com/s/asn9wp1f9sjg4f6/Count%20Unique%20Occurance.xls

 

[Elephant]

Debray:

Just saw the your workbook.It's great. I got the most primitive solution from the link Luke posted, but yours is more sofidticated, so I'll have to do some forensics by myself to totally understand your solution.

Thanxs again to all for your fast answer.

 

[Elephant]

Just thinking about...could it be possible to do it without help column and without macros?

I was trying a formula like this:


=SUM(1/COUNT.IF(IF(IF(var_criteria_rng=condicion;ROW(var_criteria_rng);0);MONTH(rng_mom_start);0);MONTH(rng_mom_start)))


But failed finding out why this doesn't work:


COUNT.IF(IF(IF(var_criteria_rng=condicion;ROW(var_criteria_rng);0);MONTH(rng_mom_start);0);MONTH(rng_mom_start)