Using function to count multiple criteria?

[matt dudek]

I need to be able to compare to columns to count when column "N" is
yes and column "R" is won.

I have scoured websites and books, and it would appear that my
equation should look like:

=SUM((N15:N524="yes")*(R15:524="won"))

Some websites have told me that I should press cntrl-shift-enter to
evaluate the equation, but that hasnt done anything for me.

Each time I try to do this calculation it either results as an
improper function, or as #VALUE!

any ideas?

Thanks in advance,
matt

 

[J.E. McGimpsey]

I need to be able to compare to columns to count when column "N" is
yes and column "R" is won.

I have scoured websites and books, and it would appear that my
equation should look like:

=SUM((N15:N524="yes")*(R15:524="won"))

Some websites have told me that I should press cntrl-shift-enter to
evaluate the equation, but that hasnt done anything for me.

Each time I try to do this calculation it either results as an
improper function, or as #VALUE!

You can array-enter (CTRL-SHIFT-ENTER or CMD-RETURN)

=SUM((N15:N524="yes")*(R15:R524="won"))

(your original formula was missing the second "R").

You must hold down the CTRL and Shift keys when pressing the Enter (or
Return) key, or the CMD key when pressing Return. If you do it
correctly, XL will display brackets around your formula:

{=SUM((N15:N524="yes")*(R15:R524="won"))}

Note that you can't put the brackets in manually.

To do it in a non-array-entered way, use SUMPRODUCT():

=SUMPRODUCT(--(N15:N524="yes"),--(R15:R524="won"))

SUMPRODUCT is a bit faster than using the array formulae. The two -'s
before each term convert the boolean TRUE/FALSE arrays into numeric
arrays (XL coerces TRUE/FALSE to 1/0 in a math operation, so using -
converte TRUE/FALSE to -1/0. The second - is there to convert the -1
back to 1).