Count if 2 conditions are true

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

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

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