=SUM(N(FREQUENCY(IF(B1:B9="",MATCH(SeatNo,SeatNo,0 )),MATCH(SeatNo,SeatNo,0))0))

ctrl+shift+enter, not just enter


" wrote:

Hi All,
I (believe) have read all the postings on counting unique entries
using SUMPRODUCT. However, to my surprise,they donot workforme!

My requirement/data is as below:

A B
SeatNo. User
A2 Peter
A2 Henry
A3 Klas
A3 John
A4
A4
A5
A5 Pat

These are seat nos. in my office where 2 users can seat- in 2 shifts.
I would like to know, (to start with) how many seats are empty. In
this example,I should get 2, (not 3).

Formulas like =SUMPRODUCT((A1:A100="")*(B1:B100<"")/
COUNTIF(A1:A100,A1:A100&"")) gave fractional values!!

Any idea,what's going wrong? I think, the formula does not work with
blank entries! We are planning to switch to macro/SQL now. If I can
get any reply before that, it would be great.

Many thanks in advance.

Regards,
~Animesh