SUM PRODUCT

[Fiona Yorke-Saville]

Hi,

Does anyone know how I can add
=COUNTIF(B19:B21=A9)

in to the following formula:-
=SUMPRODUCT(1/COUNTIF(E19:E23,E19:E23&""))

I want it to do both things at the same time but cannot work out where/how
to put it???

Thanks
Fiona

 

[Stefi]

Can you tell us in plain words what do you want to do and provide a sample of
your data structure?

Regards,
Stefi

„Fiona Yorke-Saville†ezt írta:

 

[Fiona Yorke-Saville]

Hi Stefi,
thanks for your e-mail, I've listed below what I want to do, does this make
sense?

A B
1 ABC

2 ABC AB/12345 IF A2:A6 = A1 then count B2:B6 (not counting any duplicates)
3 BCD AB/12345 The answer I need is 3
4 CDE AB/23584
5 ABC AB/32586
6 ABC AB/36987

 

[Stefi]

Try this:

=CEILING(SUMPRODUCT(--($A$1=$A$2:$A$6),1/COUNTIF(B2:B6,B2:B6&"")),1)

Regards,
Stefi

„Fiona Yorke-Saville†ezt írta:

 

[Fiona Yorke-Saville]

Thanks Stefi! That's exactly it

Try this:

=CEILING(SUMPRODUCT(--($A$1=$A$2:$A$6),1/COUNTIF(B2:B6,B2:B6&"")),1)

Regards,
Stefi

„Fiona Yorke-Saville†ezt írta:

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Fiona Yorke-Saville†ezt írta: