countif"s

C

cjjoo

this is my problem:

A b C
x123 completed
y456 complete
x123 completed 2
z456 completed
y456 complete 2
x123 completed 3



in column C , i was thinking of counting the number of times, x12
completed was in the datas. So in C , the results will be update
(as shown above)

Is there a need to have another column to joined A and B before the
can be counted
 
P

Paul Sheppard

cjjoo said:
this is my problem:

A b C
x123 completed
y456 complete
x123 completed 2
z456 completed
y456 complete 2
x123 completed 3



in column C , i was thinking of counting the number of times, x12
completed was in the datas. So in C , the results will be update
(as shown above)

Is there a need to have another column to joined A and B before the
can be counted?
Click to expand...

Hi cjjoo

Your method of joining (use CONCATANATE function) would work, n but i
you don't want to do this you could use an array formula like the on
below:

{=SUM((A1:A15="x")*(B1:B15=123))}

To enter as an array type everything inside the { } brackets into th
required cell then CTRL/SHIFT/ENTE
 
B

Bob Phillips

No, you can simply use

=SUMPRODUCT(--($A$1:A1=A1),--($B$1:B1="completed"))

in C1 and copy down. This will put 1 in the first occurrence. OIf this is
not what is wanted, then use

=IF(SUMPRODUCT(--($A$1:A1=A1),--($B$1:B1="completed"))
,"",SUMPRODUCT(--($A$1:A1=A1),--($B$1:B1="completed")))


--

HTH

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