"Tyro's SUMPRODUCT isn't taking the product of anything, just the sum, since
the product is done by the * operator."

I rest my case. You have contradicted yourself. You say my formula isn't
taking the product of anything but yet it sums the products produced by the
* operator.
You can use other operators with SUMPRODUCT such as +, -, /, ^. You can mix
and match. It works.

Tyro

"Jon Peltier" wrote in message
...
By the way, the array solution that I alluded to would be taking Tyro's
formula out of the SUMPRODUCT, put it into SUM instead, and using
CTRL+SHIFT+ENTER to enter it. This puts curly braces around the formula so
it looks like

{=SUM((A2:A300=F12)*(C2:C300=F17))}

Tyro's SUMPRODUCT isn't taking the product of anything, just the sum,
since the product is done by the * operator. But SUMPRODUCT treats the
expression as an array without needing CTRL+SHIFT+ENTER.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
I read a discussion of the different ways to turn True/False into 1/0. You
could use (A2:A300=F12)*1, (A2:A300=F12)+0, --(A2:A300=F12), or
(A2:A300=F12) in a longer expression as Tyro has done. I always used *1,
because -- looks kind of hinky to me, but according to this discussion
the -- approach was slightly faster than the others. I don't recall where
I read this, it was at least several months ago, but you could Google for
'unary minus'.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"PCLIVE" wrote in message
...
Thanks for the feedback. Just as an FYI, I don't know if you tried
Tyro's formula =SUMPRODUCT((A2:A300=F12)*(C2:C300=F17))

but that is actually just a slightly different way to write the
SUMPRODUCT formula in order to achieve the same result. I personally
prefer the way I did it because I think it's cleaner and easy to read.

=SUMPRODUCT(--(A2:A300=F12),--(C2:C300=F17))

Thanks again for the feedback.
Paul


--

"HenderH" wrote in message
...
Brilliant "PCLIVE" a) You spec my problem exactly b) your solution
worked!!

Thank you.

Kind Regards


"PCLIVE" wrote:

Maybe this:

=SUMPRODUCT(--(A2:A300=F12),--(C2:C300=F17))

If I understand you correctly, this formula will count how many times
these
two conditions are met within rows 2:300. Column A must match
criteria F12
and Column C must match criteria F17. Correct?

HTH,
Paul


--

"HenderH" wrote in message
...
I have got a working spreadsheet in 2007 and I want to give a copy to
a
colleague.

The problem is that he he still uses 2003

How do I get 2003 to do this "=COUNTIFS(A2:A300,F12,C2:C300,F17)"