counting using several criteria

[Jesse]

Hello all,

I have searched this newsgroup and it seems like this is a topic that
comes up frequently, but so far I've found no remedy. I have a large
list of items, each one has specific criteria (e.g. age, ID), and I am
trying to create a tally. I need to create a formula (I'd like to
stay away from macros) which can count the number of items according
to three different criteria. As an example if I'm dealing with a list
of apples and oranges I would want to count the number of oranges, but
only those grown in california AND sweet AND less than 2 weeks old.
Any ideas? I've been struggling with this for a while, and any help
would be greatly appreciated.

Thanks

 

[J.E. McGimpsey]

Hello all,

I have searched this newsgroup and it seems like this is a topic that
comes up frequently, but so far I've found no remedy. I have a large
list of items, each one has specific criteria (e.g. age, ID), and I am
trying to create a tally. I need to create a formula (I'd like to
stay away from macros) which can count the number of items according
to three different criteria. As an example if I'm dealing with a list
of apples and oranges I would want to count the number of oranges, but
only those grown in california AND sweet AND less than 2 weeks old.
Any ideas? I've been struggling with this for a while, and any help
would be greatly appreciated.

Assume fruit type in column A, source location in column B and date
in column C:

=SUMPRODUCT(--(A1:A1000="oranges"),--(B1:B1000="California"),
--(C1:C1000>(TODAY()-14)))

The -- convert the boolean TRUE/FALSE values returned by the
comparisons to 1/0, respectively, since SUMPRODUCT requires numeric
arrays rather than boolean.