Countif Problem
 

Hi, I posted a problem a few days ago, and got some good feedback,
unfortunately the results did not give me what I was hoping for, so, maybe I
did not explain the problem in enough detail, IN the example below I want to
count the number of codes ( column B ) that relate to Pens ( column A ), so
the answer to the below would be 3, as there are duplictae codes, the codes
always change so I cannot use a straight Countif, any ideas??

A B
Product Code
1 Pens 12345
2 Pens 12345
3 Pens 22334
4 Pens 22334
5 Pens 12356
6 Pencil 22336
7 Books 33556

Countif Problem
 

My reply would give you want you want, namely

=SUM(--(FREQUENCY(IF(A2:A100="Pens",MATCH(B2:B100,B2:B100 ,0)),
ROW(INDIRECT("1:"&ROWS(B2:B100))))0))

entered as an array formula, so committed with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Moore" wrote in message
...
Hi, I posted a problem a few days ago, and got some good feedback,
unfortunately the results did not give me what I was hoping for, so, maybe
I
did not explain the problem in enough detail, IN the example below I want
to
count the number of codes ( column B ) that relate to Pens ( column A ),
so
the answer to the below would be 3, as there are duplictae codes, the
codes
always change so I cannot use a straight Countif, any ideas??

A B
Product Code
1 Pens 12345
2 Pens 12345
3 Pens 22334
4 Pens 22334
5 Pens 12356
6 Pencil 22336
7 Books 33556

Countif Problem
 

Hi Bob, Thanks, worked a treat. one further quetion, can I enter an iFISERROR
statement in an array formula?

"Bob Phillips" wrote:

My reply would give you want you want, namely

=SUM(--(FREQUENCY(IF(A2:A100="Pens",MATCH(B2:B100,B2:B100 ,0)),
ROW(INDIRECT("1:"&ROWS(B2:B100))))0))

entered as an array formula, so committed with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Moore" wrote in message
...
Hi, I posted a problem a few days ago, and got some good feedback,
unfortunately the results did not give me what I was hoping for, so, maybe
I
did not explain the problem in enough detail, IN the example below I want
to
count the number of codes ( column B ) that relate to Pens ( column A ),
so
the answer to the below would be 3, as there are duplictae codes, the
codes
always change so I cannot use a straight Countif, any ideas??

A B
Product Code
1 Pens 12345
2 Pens 12345
3 Pens 22334
4 Pens 22334
5 Pens 12356
6 Pencil 22336
7 Books 33556

Countif Problem
 

Can you explain how you want ISERROR applied?

In article ,
"John Moore" wrote:

Hi Bob, Thanks, worked a treat. one further quetion, can I enter an iFISERROR
statement in an array formula?

"Bob Phillips" wrote:

My reply would give you want you want, namely

=SUM(--(FREQUENCY(IF(A2:A100="Pens",MATCH(B2:B100,B2:B100 ,0)),
ROW(INDIRECT("1:"&ROWS(B2:B100))))0))

entered as an array formula, so committed with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Moore" wrote in message
...
Hi, I posted a problem a few days ago, and got some good feedback,
unfortunately the results did not give me what I was hoping for, so, maybe
I
did not explain the problem in enough detail, IN the example below I want
to
count the number of codes ( column B ) that relate to Pens ( column A ),
so
the answer to the below would be 3, as there are duplictae codes, the
codes
always change so I cannot use a straight Countif, any ideas??

A B
Product Code
1 Pens 12345
2 Pens 12345
3 Pens 22334
4 Pens 22334
5 Pens 12356
6 Pencil 22336
7 Books 33556

Countif Problem
 

What error condition? Surely, there is 0,1,..., n occurrences, no errors?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
My reply would give you want you want, namely

=SUM(--(FREQUENCY(IF(A2:A100="Pens",MATCH(B2:B100,B2:B100 ,0)),
ROW(INDIRECT("1:"&ROWS(B2:B100))))0))

entered as an array formula, so committed with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Moore" wrote in message
...
Hi, I posted a problem a few days ago, and got some good feedback,
unfortunately the results did not give me what I was hoping for, so,
maybe
I
did not explain the problem in enough detail, IN the example below I
want
to
count the number of codes ( column B ) that relate to Pens ( column A ),
so
the answer to the below would be 3, as there are duplictae codes, the
codes
always change so I cannot use a straight Countif, any ideas??

A B
Product Code
1 Pens 12345
2 Pens 12345
3 Pens 22334
4 Pens 22334
5 Pens 12356
6 Pencil 22336
7 Books 33556

Countif Problem
 

You're probably getting #N/A because Column B contains one or more cells
that are empty. To allow empty cells, Bob's formula can be tweaked as
follows...

=SUM(--(FREQUENCY(IF((A2:A100="Pens")*(B2:B100<""),MATCH (B2:B100,B2:B100
,0)),ROW(INDIRECT("1:"&ROWS(B2:B100))))0))

Actually, since Column B contains numbers, you can use the following
formula instead, which would eliminate three function calls...

=SUM(--(FREQUENCY(IF((A2:A100="Pens")*(B2:B100<""),B2:B1 00),IF((A2:A100=
"Pens")*(B2:B100<""),B2:B100))0))

Hope this helps!

In article ,
"John Moore" wrote:

Hi Bob, Thanks, worked a treat. one further quetion, can I enter an iFISERROR
statement in an array formula?

"Bob Phillips" wrote:

My reply would give you want you want, namely

=SUM(--(FREQUENCY(IF(A2:A100="Pens",MATCH(B2:B100,B2:B100 ,0)),
ROW(INDIRECT("1:"&ROWS(B2:B100))))0))

entered as an array formula, so committed with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Moore" wrote in message
...
Hi, I posted a problem a few days ago, and got some good feedback,
unfortunately the results did not give me what I was hoping for, so, maybe
I
did not explain the problem in enough detail, IN the example below I want
to
count the number of codes ( column B ) that relate to Pens ( column A ),
so
the answer to the below would be 3, as there are duplictae codes, the
codes
always change so I cannot use a straight Countif, any ideas??

A B
Product Code
1 Pens 12345
2 Pens 12345
3 Pens 22334
4 Pens 22334
5 Pens 12356
6 Pencil 22336
7 Books 33556