Re: Count if 2 conditions are true

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

=SUMPRODUCT(--($J$13:$J$110=X2),--($L$3:$L$110="Y"))

where X2 houses a city of interest.

Thrava wrote: 
Hi folks,
This ought to be easy, but I can't get it to work

In range J13:J110 I have various city names where a new customer has been added. There can be multiple occurances of each city as we go down the colum range of J13:j110. So there may be 5 Chicago, 2 Denver etc.
In range L13:L110 I have either "Y" or Blank in front of the of the cities.

I want to write a formula that for each city, it counts how many "Y" there are in range L13:L110.

Any suggestions please?

Thanks 

--

[1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence.
.

Relevant Pages

  • Re: Count if 2 conditions are true
    ... > where X2 houses a city of interest. ... >> Hi folks, ... >> This ought to be easy, but I can't get it to work ... > The SumProduct function should implicitly coerce the truth values to ...
    (microsoft.public.excel.worksheet.functions)
  • Count if 2 conditions are true
    ... Hi folks, ... This ought to be easy, but I can't get it to work ... In range J13:J110 I have various city names where a new customer has been ...
    (microsoft.public.excel.worksheet.functions)
  • Re: The Industry choice
    ... all the way to the Northern border of the city. ... >given shop from the POV of a given customer. ... energy available at low prices at special times in other countries ...
    (comp.lang.python)
  • Re: SQL
    ... > you showed me precisely a solution that uses an RDBMS, ... query or part of a query. ... > Customer *customer; ... How do you navigate to the city object, ...
    (comp.object)
  • Re: To hibernate or not to hibernate?
    ... or should I use Hibernate to take care of the DB stuff? ... I have a Customer object and can pass data ... City and Province objects (I would use String city and int cityId, ... Imagine I have a list of cities ...
    (comp.lang.java.databases)