logic

  • Thread starter Thread starter Merritt Keiser
  • Start date Start date
M

Merritt Keiser

What is the logic you enter to count the number of
occurences in one column only if it matches up to a
specific criteria in a different column?
 
AFAIK - Countif doesn't give the option to poll another range (column) for
criteria.

Possibly, SumProduct is the better choice.

However, the Help files on SumProduct are very lacking.

A simple example might suffice.

Column A displays apples, oranges, and pears in a random manner.
Column B displays the various prices for the fruit at market closing times
during the season.

You wish to know how many times, if any, oranges sold for $0.10/lb.

==SUMPRODUCT((A1:A25="oranges")*(B1:B25=0.1))

However, this limits your formula to oranges and to $0.10/lb.
To change criteria, you would have to modify the formula.

A more versatile approach would be to enter cell locations into the formula,
therefore allowing a simple data entry into a specified cell to accomplish a
criteria change.

Let's pick C1 for the entry of the selected fruit, and D1 for the sought
after pricing close.

=SUMPRODUCT((A1:A25=C1)*(B1:B25=D1))

Now, you have the ability to enter and/or change criteria easily and quickly
by just typing into the specified cells.


HTH,

RD
========================================
Please keep all correspondence within the Group, so all may benefit!
========================================
 
Back
Top