COUNTIF & DISTINCT?

[alon.weinstein]

Hello.

I'm trying to find a way to do the following: I'm building some sort of an expense reporting form:

Column A | Column B
1/1/2009 | Hotel
1/1/2009 | Restaurant
1/2/2009 | Hotel
1/4/2009 | Flight

I need to count the (distinct!) number of dates where a Hotel is expensed, and the number of days when a Hotel was not expensed.

I know that when a Hotel is expensed that specific day will have only one line in the report with the Hotel in column B, so I can use COUNTIF to get that part done.

The 2nd part is proving to be trickier. Finding days with a Hotel was easy because they are guaranteed to be in the report, however days without a Hotel entry simply won't have one, so I need to figure out a way to count them.

Ideally I wanted to find a way to get a list of distinct Date values from lines that _do not_ have Hotel in them. In the report each date might repeat several times which is why it should be a distinct list.

Basically, I guess I'm looking for a way to do a DISTINCT * COUNTIF, but I can't find a DISTINCT function... any ideas how to accomplish this?


Thanks,
Alon

[alon.weinstein]

Somehow when you try to put your problem in writing you find a solution. Here's what I came up with after some more digging around:

I use MATCH() with match type="0" to find the first occurrence of a date within all the rows above it. I then compare the result with the the row's actual position in the list and if they don't match I know it's the first occurrence of this date, which means it's unique. Then I use COUNTIF to find the number of rows where the result of the formula was True.

The functions I use (this is to test row 4 -- the list starts at row 2) (Column A has the dates):

=NOT(MATCH($A2;$A$2:$A4;0)<>(ROW($A4)-ROW($A$2)+1))

which translates to:

* Look at the list of items A2:A[current row]
* Get first occurrence of the date listed in [current row] in the overall list
* If the result of previous function is different then (the current row's number - the first row's number + 1) then write TRUE in the cell, if not write FALSE (the +1 is needed to offset 1 instead of 0 like the MATCH function does)


If anyone has a more elegant way I'd be happy to hear it -- I'm posting this in case anyone faces the same question in the future.


Alon

[ken johnson]

I'm not too sure I follow what you are doing, however...

=COUNTIF($A$2:$A2;A2)=1

returns the same result when applied to the four sample rows supplied; i.e...

TRUE
FALSE
TRUE
TRUE

Ken Johnson