=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.
Bookmarks