Help with COUNTIF formula

[carl_birchall]

I thought this would be a fairly simple formula but just dont seem to
be able to get the right syntax.

Rows in column B will contain the text, 'Nichs' or 'NMSFC'.
Rows in column C may contain Chr149 ”

At bottom of column C I need a count, where column B = 'NMSFC' and
column C has a ” in it.

I had a go at this with the following formula, but it doesn't work. I
have tried using AND but again cant quite get it right.
=IF($B:$B="NMSFC",COUNTIF(C5:C263,"*"),"")

Any advice or help would be appreciated as completely stuck and cant
find a previous post that helps.

Thanks Carl

 

[PCLIVE]

Here's one way:
You'll need to specify an actual range rather than the entire column.

=SUMPRODUCT(--(A2:A20="NMSFC"),--(B2:B20="”"))

Adjust your range as needed.

HTH,
Paul

--

I thought this would be a fairly simple formula but just dont seem to
be able to get the right syntax.

Rows in column B will contain the text, 'Nichs' or 'NMSFC'.
Rows in column C may contain Chr149 ”

At bottom of column C I need a count, where column B = 'NMSFC' and
column C has a ” in it.

I had a go at this with the following formula, but it doesn't work. I
have tried using AND but again cant quite get it right.
=IF($B:$B="NMSFC",COUNTIF(C5:C263,"*"),"")

Any advice or help would be appreciated as completely stuck and cant
find a previous post that helps.

Thanks Carl

 

[PCLIVE]

Just noticed that after the message was posted, it no longer included
Chr149. Replace the middle double quotes with Chr149. I'm sure you
probably figured that out though.

Regards,
Paul

 

[Dave Peterson]

just in case...

=SUMPRODUCT(--(A2:A20="NMSFC"),--(B2:B20=char(149)))

 

[PCLIVE]

Good point!

--

just in case...

=SUMPRODUCT(--(A2:A20="NMSFC"),--(B2:B20=char(149)))

 

[carl_birchall]

Good point!

--






- Show quoted text -

Thanks for this, worked a treat. Never even considered using
SUMPRODUCT.
Thanks again