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

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

Thank you Aladin.
This is what I was looking for

"Aladin Akyurek" wrote:

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