I need to count the diferent articles of a column where the result its
between an interval (the criteria is defined in two cells).
in my datasheet its possible that the same article has different results.

example:
Data
____________________
(articles) (result)
Column A Column B
aaa 7
bbb 7
aaa 15
aaa 6
aaa 8

criteria
____________________
Column C Column D
5 10

Analysing the result, i have 3 "aaa" and 1 "bbb".
I want to konw a formula that the result result is 2 articles (artilce "aaa"
+ article "bbb").
thanks,
gmfp

=SUMPRODUCT(--(A2:A20="aaa"),--($B$2:$B$20>=$C$2),--($B$2:$B$20<=$D$2))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"gmfp" <(E-Mail Removed)> wrote in message
news:4C95F8A7-0717-4759-A3A5-(E-Mail Removed)...
> I need to count the diferent articles of a column where the result its
> between an interval (the criteria is defined in two cells).
> in my datasheet its possible that the same article has different results.
>
> example:
> Data
> ____________________
> (articles) (result)
> Column A Column B
> aaa 7
> bbb 7
> aaa 15
> aaa 6
> aaa 8
>
> criteria
> ____________________
> Column C Column D
> 5 10
>
> Analysing the result, i have 3 "aaa" and 1 "bbb".
> I want to konw a formula that the result result is 2 articles (artilce
"aaa"
> + article "bbb").
> thanks,
> gmfp

Bob,

i dont want to count the articles that are equal "aaa".
i want to count how many different articles i have.
and i prefer not to identify the article in the formula, because i could
have 40 different articles.
in the example, i have 3 "aaa" and 1 "bbb" that satisfy my criteria. so i
have 2 different articles. this is the result that i want to know.
tks
gmfp

"Bob Phillips" escreveu:

> =SUMPRODUCT(--(A2:A20="aaa"),--($B$2:$B$20>=$C$2),--($B$2:$B$20<=$D$2))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "gmfp" <(E-Mail Removed)> wrote in message
> news:4C95F8A7-0717-4759-A3A5-(E-Mail Removed)...
> > I need to count the diferent articles of a column where the result its
> > between an interval (the criteria is defined in two cells).
> > in my datasheet its possible that the same article has different results.
> >
> > example:
> > Data
> > ____________________
> > (articles) (result)
> > Column A Column B
> > aaa 7
> > bbb 7
> > aaa 15
> > aaa 6
> > aaa 8
> >
> > criteria
> > ____________________
> > Column C Column D
> > 5 10
> >
> > Analysing the result, i have 3 "aaa" and 1 "bbb".
> > I want to konw a formula that the result result is 2 articles (artilce
> "aaa"
> > + article "bbb").
> > thanks,
> > gmfp
>
>
>

=SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100)))

ctrl+shift+enter (not just enter)



"gmfp" wrote:

> I need to count the diferent articles of a column where the result its
> between an interval (the criteria is defined in two cells).
> in my datasheet its possible that the same article has different results.
>
> example:
> Data
> ____________________
> (articles) (result)
> Column A Column B
> aaa 7
> bbb 7
> aaa 15
> aaa 6
> aaa 8
>
> criteria
> ____________________
> Column C Column D
> 5 10
>
> Analysing the result, i have 3 "aaa" and 1 "bbb".
> I want to konw a formula that the result result is 2 articles (artilce "aaa"
> + article "bbb").
> thanks,
> gmfp