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

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
_______


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

Please see PCLIVEs Solution

"Jon Peltier" wrote:

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
_______


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

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

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

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

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

Regards,

Tyro

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

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

--

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

Regards,

Tyro

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

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

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

=SUMPRODUCT((A2:A300=F12)*(C2:C300=F17))

Tyro

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

Not quite - Please see PCLIVE solution

"Tyro" wrote:

=SUMPRODUCT((A2:A300=F12)*(C2:C300=F17))

Tyro

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

Yes Quite. Did you try my formula?

Tyro


"HenderH" wrote in message
...
Not quite - Please see PCLIVE solution

"Tyro" wrote:

=SUMPRODUCT((A2:A300=F12)*(C2:C300=F17))

Tyro

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

Not quite!

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


"Tyro" wrote in message
...
Yes Quite. Did you try my formula?

Tyro


"HenderH" wrote in message
...
Not quite - Please see PCLIVE solution

"Tyro" wrote:

=SUMPRODUCT((A2:A300=F12)*(C2:C300=F17))

Tyro

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

Quite

My formula works

Tyro

"Jon Peltier" wrote in message
...
Not quite!

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


"Tyro" wrote in message
...
Yes Quite. Did you try my formula?

Tyro


"HenderH" wrote in message
...
Not quite - Please see PCLIVE solution

"Tyro" wrote:

=SUMPRODUCT((A2:A300=F12)*(C2:C300=F17))

Tyro

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