conditional counting

R

Ridimz

For the following table:

Apple gsmith1 bad
Banana dwarf good
Apple washin1 bad
Apple gsmith1 good

I'd like get a total count of all only bad apples.
How would I go about it?

Thanks in advance!
Ridimz
 
A

Arvi Laanemets

Hi

=SUMPRODUCT((NameRange="Apple")*(ConditionRange="bad"))
where NameRange and ConditionRange are references to ranges (columns) with
data. The formula returns the count of rows with both conditions filled at
same time.

When you have also a column where quantities of fruits are entered, you can
also summarize quantities - the formula will be
=SUMPRODUCT((NameRange="Apple")*(ConditionRange="bad")*(QuantityRange))

You can also have more conditions. P.e. the formula
=SUMPRODUCT((NameRange="Apple")*(PeopleRange="dwarf")*(ConditionRange="bad")
*(QuantityRange))
summarizes all bad apples owned by dwarf
 
Top