Countifs Fx in 07 how in 03?

H

HenderH

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)"
 
J

Jon Peltier

Excel 2003 won't calculate that; COUNTIFS was introduced in Excel 2007. You
could code a UDF (which would run in 2007 in place of the new built-in
function, by the way), or you could use a good old array formula.

- Jon
 
P

PCLIVE

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
 
H

HenderH

Brilliant "PCLIVE" a) You spec my problem exactly b) your solution worked!!

Thank you.

Kind Regards
 
H

HenderH

Please see PCLIVEs Solution

Jon Peltier said:
Excel 2003 won't calculate that; COUNTIFS was introduced in Excel 2007. You
could code a UDF (which would run in 2007 in place of the new built-in
function, by the way), or you could use a good old array formula.

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

PCLIVE

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


--
 
T

Tyro

:) I prefer my way because I'm a programmer and instead of using "--"
twice, I use "*" once. To each his own.

Regards,

Tyro
 
P

PCLIVE

I wasn't saying that one way is better than another. I was simply trying to
point out that had they tried your formula, they would have seen that it is
just another way to write a SUMPRODUCT formula to achieve the same result.
But I guess they didn't trying it before shooting it down.

Regards,
Paul

--
 
T

Tyro

There are always a half a dozen different ways of doing the same thing.
Which way is best, like beauty, is in many cases only in the eye of the
beholder.
I was not implying that your way was inferior. I know the person didn't try
my formula. But, no matter.

Regards,

Tyro
 
J

Jon Peltier

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
 
J

Jon Peltier

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
 
T

Tyro

Does my formula work?

Tyro

Jon Peltier said:
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
_______
Click to expand...
 
T

Tyro

"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
 
J

Jon Peltier

No, there's no contradiction. I said the SUMPRODUCT doesn't take the product
of anything. The syntax is SUMPRODUCT(array1,array2,...), where the elements
of the arrays are multiplied then added. You only have one array (whose
elements are already multiplied within the definition of the element), so
its elements are simply added.

If I use this formula:
=SUMPRODUCT({1,2,3})
I have no multiplication, but I still get the sum of the elements, which is
6. If I use this formula:
=SUMPRODUCT({1,2,3},{2,2,2})
I get 12, because each element in the first array is multiplied by the
corresponding element in the second array (which are all 2).

I'm not criticizing your formula, because it works. I'm just trying to
explain the various approaches.

- Jon
 
J

Jon Peltier

Lighten up. Read your post, then read my response:

"Did you try my formula?"

"Not quite"

I was poking a little fun at the OP's response to your suggestion. I know
your formula works, but I think the OP didn't even try it.

- Jon
 
Top