SUM PRODUCT
 

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

SUM PRODUCT
 

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:

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

SUM PRODUCT
 

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" wrote:

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:

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

SUM PRODUCT
 

Try this:

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

Regards,
Stefi

€žFiona Yorke-Saville€� ezt Ã*rta:

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" wrote:

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:

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

SUM PRODUCT
 

Thanks Stefi! That's exactly it :) :)

"Stefi" wrote:

Try this:

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

Regards,
Stefi

€žFiona Yorke-Saville€� ezt Ã*rta:

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" wrote:

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:

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

SUM PRODUCT
 

You are welcome! Thanks for the feedback!
Stefi

€žFiona Yorke-Saville€� ezt Ã*rta:

Thanks Stefi! That's exactly it :) :)

"Stefi" wrote:

Try this:

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

Regards,
Stefi

€žFiona Yorke-Saville€� ezt Ã*rta:

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" wrote:

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:

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