Counting Tariffs

[cj21]

I have a list of products, each of which has a tarifff. e.g.

Product...Tariff
01234563 10
01423467 10
01423578 20
13466989 5
10457904 25
14456893 30
20345076 5
34056097 20

I want a formula that counts how many different tariff values there are. In this example there are 5. Is this possible?

Thanks

Chris

[bob777]

Why not put a pivot table to the side of your data and use it to list all tariffs - as a bonus it will tell you how many of each there are.

[Niek Otten]

Hi Chris,

Don't ask me how it works. I just copied it from a Google search.

=SUMPRODUCT((B1:B8<>"")/COUNTIF(B1:B8,B1:B8&""))

--
Kind regards,

Niek Otten

"cj21" <cj21.2381on_1139924401.4259@excelforum-nospam.com> wrote in message
news:cj21.2381on_1139924401.4259@excelforum-nospam.com...
>
> I have a list of products, each of which has a tarifff. e.g.
>
> Product...Tariff
> 01234563 10
> 01423467 10
> 01423578 20
> 13466989 5
> 10457904 25
> 14456893 30
> 20345076 5
> 34056097 20
>
> I want a formula that counts how many different tariff values there
> are. In this example there are 5. Is this possible?
>
> Thanks
>
> Chris
>
>
> --
> cj21
> ------------------------------------------------------------------------
> cj21's Profile:
> http://www.excelforum.com/member.php...o&userid=25673
> View this thread: http://www.excelforum.com/showthread...hreadid=512219
>

[Bob Phillips]

Hi Niek,

I had a go at explaining it a while back in http://tinyurl.com/dhbxe

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:Oojmc2WMGHA.2696@TK2MSFTNGP14.phx.gbl...
> Hi Chris,
>
> Don't ask me how it works. I just copied it from a Google search.
>
> =SUMPRODUCT((B1:B8<>"")/COUNTIF(B1:B8,B1:B8&""))
>
> --
> Kind regards,
>
> Niek Otten
>
> "cj21" <cj21.2381on_1139924401.4259@excelforum-nospam.com> wrote in
message
> news:cj21.2381on_1139924401.4259@excelforum-nospam.com...
> >
> > I have a list of products, each of which has a tarifff. e.g.
> >
> > Product...Tariff
> > 01234563 10
> > 01423467 10
> > 01423578 20
> > 13466989 5
> > 10457904 25
> > 14456893 30
> > 20345076 5
> > 34056097 20
> >
> > I want a formula that counts how many different tariff values there
> > are. In this example there are 5. Is this possible?
> >
> > Thanks
> >
> > Chris
> >
> >
> > --
> > cj21
> > ------------------------------------------------------------------------
> > cj21's Profile:
> > http://www.excelforum.com/member.php...o&userid=25673
> > View this thread:
http://www.excelforum.com/showthread...hreadid=512219
> >
>
>

[Niek Otten]

Thanks, Bob!


--
Kind regards,

Niek Otten

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:efdzt%23WMGHA.2916@tk2msftngp13.phx.gbl...
> Hi Niek,
>
> I had a go at explaining it a while back in http://tinyurl.com/dhbxe
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:Oojmc2WMGHA.2696@TK2MSFTNGP14.phx.gbl...
>> Hi Chris,
>>
>> Don't ask me how it works. I just copied it from a Google search.
>>
>> =SUMPRODUCT((B1:B8<>"")/COUNTIF(B1:B8,B1:B8&""))
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "cj21" <cj21.2381on_1139924401.4259@excelforum-nospam.com> wrote in
> message
>> news:cj21.2381on_1139924401.4259@excelforum-nospam.com...
>> >
>> > I have a list of products, each of which has a tarifff. e.g.
>> >
>> > Product...Tariff
>> > 01234563 10
>> > 01423467 10
>> > 01423578 20
>> > 13466989 5
>> > 10457904 25
>> > 14456893 30
>> > 20345076 5
>> > 34056097 20
>> >
>> > I want a formula that counts how many different tariff values there
>> > are. In this example there are 5. Is this possible?
>> >
>> > Thanks
>> >
>> > Chris
>> >
>> >
>> > --
>> > cj21
>> > ------------------------------------------------------------------------
>> > cj21's Profile:
>> > http://www.excelforum.com/member.php...o&userid=25673
>> > View this thread:
> http://www.excelforum.com/showthread...hreadid=512219
>> >
>>
>>
>
>

[Gary''s Student]

For say 100 items:

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))


--
Gary''s Student


"cj21" wrote:

>
> I have a list of products, each of which has a tarifff. e.g.
>
> Product...Tariff
> 01234563 10
> 01423467 10
> 01423578 20
> 13466989 5
> 10457904 25
> 14456893 30
> 20345076 5
> 34056097 20
>
> I want a formula that counts how many different tariff values there
> are. In this example there are 5. Is this possible?
>
> Thanks
>
> Chris
>
>
> --
> cj21
> ------------------------------------------------------------------------
> cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
> View this thread: http://www.excelforum.com/showthread...hreadid=512219
>
>