Excel 2000 - Formulas

[chiefny]

I am using a spread sheet in Excel 2000 that contains names and addresses and a person identifier code.
The State is in Col X 3-1412 the identifier is in Col Q 3-1412. I am trying to count with two conditions.
One State by Active identifier. I am using the following formulas and then hit ctrl shift and enter but
get #NAME? =SUM(IF($X$3:$X$1412=pa,IF($Q$3:$Q$1412=DD****,1),0))

The identifiers are DD1038
NA1039
MR1040

Please help!

[NateO]

Try the following:

=SUM(($X$3:$X$1412="pa")*($Q$3:$Q$1412="DD****"))

And press ctrl-shift-enter.

Hope this helps.

[Aladin Akyurek]

On 2002-05-14 11:12, chiefny wrote:
I am using a spread sheet in Excel 2000 that contains names and addresses and a person identifier code.
The State is in Col X 3-1412 the identifier is in Col Q 3-1412. I am trying to count with two conditions.
One State by Active identifier. I am using the following formulas and then hit ctrl shift and enter but
get #NAME? =SUM(IF($X$3:$X$1412=pa,IF($Q$3:$Q$1412=DD****,1),0))

The identifiers are DD1038
NA1039
MR1040

Please help!

If you want to include the variants of an identifier in the count, use:

=SUMPRODUCT(($X$3:$X$1412="pa")*(ISNUMBER(SEARCH("DD",$Q$3:$Q$1412))))

If the count must be per state and per individual identifier, use:

=SUMPRODUCT(($X$3:$X$1412="pa")*($Q$3:$Q$1412="DD1038")

[Yogi Anand]

Hi Nate:

=SUM(($X$3:$X$1412="pa")*($Q$3:$Q$1412="DD****"))

The OP's intent to use **** as wildcards with DD will not be acceptable in the SUM function.

Regards!

[NateO]

The OP's intent to use **** as wildcards with DD will not be acceptable in the SUM function.

Good catch Yogi!

We can wildcard the array sum function. I've provided one [pertinent] example below:


=SUM(($X$3:$X$1412="pa")*(LEFT($Q$3:$Q$1412,2)="DD"))

Simply press ctrl-shift-enter. Make sure when you try this that the formula is surrounded by { }.

_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-05-15 19:50 ]