count articles of a column that the result is between an intervall

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gmfp - 20 Nov 2006 12:20 GMT

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

Reply to this Message

Bob Phillips - 20 Nov 2006 12:55 GMT

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

Signature

HTH

Bob Phillips

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

> I need to count the diferent articles of a column where the result its
> between an interval (the criteria is defined in two cells).
[quoted text clipped - 21 lines]
> thanks,
> gmfp

Reply to this Message

gmfp - 20 Nov 2006 13:31 GMT

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

> =SUMPRODUCT(--(A2:A20="aaa"),--($B$2:$B$20>=$C$2),--($B$2:$B$20<=$D$2))
>
[quoted text clipped - 24 lines]
> > thanks,
> > gmfp

Reply to this Message

Teethless mama - 20 Nov 2006 16:03 GMT

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

ctrl+shift+enter (not just enter)

> I need to count the diferent articles of a column where the result its
> between an interval (the criteria is defined in two cells).
[quoted text clipped - 21 lines]
> thanks,
> gmfp

Reply to this Message