Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank unique entries
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank unique entries
wrote in message
... 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="")) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank unique entries
=SUM(--(FREQUENCY(IF(B2:B9="",MATCH(A2:A9,A2:A9,0)),ROW(I NDIRECT("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) wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank unique entries
=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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank unique entries
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank unique entries
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) wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique entries with criteria | Excel Discussion (Misc queries) | |||
Counting Unique Entries | Excel Discussion (Misc queries) | |||
Counting unique entries | Excel Discussion (Misc queries) | |||
Counting Unique Entries | Excel Discussion (Misc queries) | |||
counting unique entries in a list | Excel Discussion (Misc queries) |