Counting occurrences of multiple conditions

[Avrum Lapin]

I am having a Senior Excel moment

I have a array covering lines 4 to 4427

Column X can contain one of A-1, A-2,Š.A-10, ŠG-10

Column Y can be blank or contain R1 or R3

I need to know how many rows with ³A-1² in column X also have an ³R1² in
Column Z.

The Excel help suggests
=SUM(IF(X4:X4430= ³A-1²,IF(Y4:Y4430="R1",1,0)))

and rather than pressing enter press z+return

Pressing z+return leaves me with a z+ in the cell

Pressing return does not give me the correct answer

Suggestions (or a book))

TIA

 

[Bob Greenblatt]

I am having a Senior Excel moment

I have a array covering lines 4 to 4427

Column X can contain one of A-1, A-2,ä.A-10, äG-10

Column Y can be blank or contain R1 or R3

I need to know how many rows with „A-1¾ in column X also have an „R1¾ in
Column Z.

The Excel help suggests
=SUM(IF(X4:X4430= „A-1¾,IF(Y4:Y4430="R1",1,0)))

and rather than pressing enter press z+return

Pressing z+return leaves me with a z+ in the cell

Pressing return does not give me the correct answer

Suggestions (or a book))

Try the following as an array formula - after entering the formula in the
formula bar, press control-enter. ?this will make it an array formula and
place brackets {} around the formula. (Don't type the {}.

=sum((x4:x4430<>"A-1")*(y4:y4430="R1"))

 

[jpdphd]

I am having a Senior Excel moment

I have a array covering lines 4 to 4427

Column X can contain one of A-1, A-2,Å .A-10, Å G-10

Column Y can be blank or contain R1 or R3

I need to know how many rows with ³A-1² in column X also have an ³R1² in
Column Z.

The Excel help suggests
   =SUM(IF(X4:X4430= ³A-1²,IF(Y4:Y4430="R1",1,0)))

   and rather than pressing enter press z+return

Pressing z+return leaves me with a z+ in the cell

Pressing return does not give me the correct answer

Suggestions (or a book))

TIA

This is one of the 'array' formulas. So you have to hold the "Command"
key (otherwise known as Apple - to the right of option) while you
press return. You will know you've got it when the formula has { }
around it. (Don't try to put the { } in by hand - just doesn't work
that way).
jpdphd

 

[Avrum Lapin]

Thanks Jim and Bob- My problem was that the on line help to get the
array function (squiggly brackets) was incorrect. They suggested z+enter

 

[JE McGimpsey]

Thanks Jim and Bob- My problem was that the on line help to get the
array function (squiggly brackets) was incorrect. They suggested z+enter

You've got a font problem -

Help uses the Wingdings font to display the ASCII character 122 (which
in non-symbol fonts is the 'z' character) as the CMD symbol.

Reenable Wingdings and you should see the symbol correctly.