Countif Problem

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Domenic]

Can you explain how you want ISERROR applied?

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

 

[Bob Phillips]

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

--

HTH

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

 

[Domenic]

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:B100),IF((A2:A100=
"Pens")*(B2:B100<>""),B2:B100))>0))

Hope this helps!