Counting blank unique entries

[animeshhere]

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

 

[Stephen]

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

I would simply restructure the data with seat number in column A, occupant
in first shift in column B and occupant in second shift in column C (which
is easier to read anyway). Then the formula is
=SUMPRODUCT((B1:B10="")*(C1:C10=""))

 

[Bob Phillips]

=SUM(--(FREQUENCY(IF(B2:B9="",MATCH(A2:A9,A2:A9,0)),ROW(INDIRECT("1:"&ROWS(A2:A9))))>0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Teethless mama]

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

ctrl+shift+enter, not just enter

 

[animeshhere]

Thanks Bob, Teethless mama.

However, both the formulae result in 3 for the following data:
a1 f
a1 g
b1 h
b1
c1
c1 p
d1
d1 p

whereas, it should show 0. (Because, no seat is free- each one is
occupied in some shift(s))

Thanks again... for your time.
~Animesh

 

[Bob Phillips]

You didn't array enter them as we explained in our answers.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)