I created some countifs in 2007, saved it down to 2003. When opened in 2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in the
range that the countifs were counting, an #name? error was produced, and it
couldn't be undone. Is there any way around this ? Though I'm still impressed
that 2003 was able to keep the 2007 results to begin with. What does xlfn
mean ?

Thanks,


Steve

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula in, it
wasn't actually calculating. It was just able to remember what value it had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to recalculate (by
adding a number) XL gave up on trying to figure it out. This is similar to
what happenes whne you use a function from an Add-in, but don't have the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in 2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in the
range that the countifs were counting, an #name? error was produced, and it
couldn't be undone. Is there any way around this ? Though I'm still impressed
that 2003 was able to keep the 2007 results to begin with. What does xlfn
mean ?

Thanks,


Steve

Hi,

The following function are new in 2007 and therefore not supported in 2003:

=SUMIFS
=COUNTIFS
=AVERAGEIF
=AVERAGEIFS
=IFERROR
and 8 cube functions.

You can duplicate the behavior of the first 4 without too much problem using
SUMPRODUCT or other function compatible to 2003, but the last ones would
probalby require VBA
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in 2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in the
range that the countifs were counting, an #name? error was produced, and it
couldn't be undone. Is there any way around this ? Though I'm still impressed
that 2003 was able to keep the 2007 results to begin with. What does xlfn
mean ?

Thanks,


Steve

shane,

I would like to know how you would replace =SUMIFS (and for that matter the
other 3). Our orgenisation has a mixture of excel versions atm and i
encounter problems like this often. If you have found a rescource that
describes these sort of work arounds that would be great.

as an example, at the moment i need to make the following backwards
compatable:

=SUMIFS($S:$S,$F:$F,$AI8,AM:AM,"<0")

thanks in advance for your help

Jam

"Shane Devenshire" wrote:

Hi,

The following function are new in 2007 and therefore not supported in 2003:

=SUMIFS
=COUNTIFS
=AVERAGEIF
=AVERAGEIFS
=IFERROR
and 8 cube functions.

You can duplicate the behavior of the first 4 without too much problem using
SUMPRODUCT or other function compatible to 2003, but the last ones would
probalby require VBA
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in 2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in the
range that the countifs were counting, an #name? error was produced, and it
couldn't be undone. Is there any way around this ? Though I'm still impressed
that 2003 was able to keep the 2007 results to begin with. What does xlfn
mean ?

Thanks,


Steve

i need to make the following backwards compatable:
=SUMIFS($S:$S,$F:$F,$AI8,AM:AM,"<0")

Try this. It'll work in all versions of Excel.

=SUMPRODUCT(--($F1:$F100=$AI8),--(AM1:AM100<0),$S1:$S100)

Note that with the SUMPRODUCT function you *can't* use entire columns as
range references in Excel versions prior to Excel 2007.


--
Biff
Microsoft Excel MVP


"jam" wrote in message
...
shane,

I would like to know how you would replace =SUMIFS (and for that matter
the
other 3). Our orgenisation has a mixture of excel versions atm and i
encounter problems like this often. If you have found a rescource that
describes these sort of work arounds that would be great.

as an example, at the moment i need to make the following backwards
compatable:

=SUMIFS($S:$S,$F:$F,$AI8,AM:AM,"<0")

thanks in advance for your help

Jam

"Shane Devenshire" wrote:

Hi,

The following function are new in 2007 and therefore not supported in
2003:

=SUMIFS
=COUNTIFS
=AVERAGEIF
=AVERAGEIFS
=IFERROR
and 8 cube functions.

You can duplicate the behavior of the first 4 without too much problem
using
SUMPRODUCT or other function compatible to 2003, but the last ones would
probalby require VBA
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in
the
range that the countifs were counting, an #name? error was produced,
and it
couldn't be undone. Is there any way around this ? Though I'm still
impressed
that 2003 was able to keep the 2007 results to begin with. What does
xlfn
mean ?

Thanks,


Steve

Hi,
I tried what you said and it doesnt seem to work. In one column i have type
of centre (whether centre is Tertiary, secondary or Primary) and in another
column i have operation start date (Month when operation started). If i want
to know how many primary centres were started in April 2008 how do i go about
doing it?

When i use SUMPRODUCT function like how you have given below, i get the
count of Primary centres but i do not get the count of primary centres
started in April 2008.

Regards
Krishnan

"Luke M" wrote:

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula in, it
wasn't actually calculating. It was just able to remember what value it had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to recalculate (by
adding a number) XL gave up on trying to figure it out. This is similar to
what happenes whne you use a function from an Add-in, but don't have the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in 2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in the
range that the countifs were counting, an #name? error was produced, and it
couldn't be undone. Is there any way around this ? Though I'm still impressed
that 2003 was able to keep the 2007 results to begin with. What does xlfn
mean ?

Thanks,


Steve

Assuming you have a column of true Excel dates...

=SUMPRODUCT(--(A1:A100="Primary"),--(TEXT(B1:B100,"myyyy")="42008"))

Or:

=SUMPRODUCT(--(A1:A100="Primary"),--(MONTH(B1:B100)=4),--(YEAR(B1:B100)=2008))

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I tried what you said and it doesnt seem to work. In one column i have
type
of centre (whether centre is Tertiary, secondary or Primary) and in
another
column i have operation start date (Month when operation started). If i
want
to know how many primary centres were started in April 2008 how do i go
about
doing it?

When i use SUMPRODUCT function like how you have given below, i get the
count of Primary centres but i do not get the count of primary centres
started in April 2008.

Regards
Krishnan

"Luke M" wrote:

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula in, it
wasn't actually calculating. It was just able to remember what value it
had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to recalculate
(by
adding a number) XL gave up on trying to figure it out. This is similar
to
what happenes whne you use a function from an Add-in, but don't have the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in
the
range that the countifs were counting, an #name? error was produced,
and it
couldn't be undone. Is there any way around this ? Though I'm still
impressed
that 2003 was able to keep the 2007 results to begin with. What does
xlfn
mean ?

Thanks,


Steve

Thank you. Info was very useful. But can i link the period to a cell. What i
mean is: Instead of mentioning "42008", can i link it to a cell which has
Apr-2008? This is to avoid manually changing period in very cell.

Regards
Krishnan

"T. Valko" wrote:

Assuming you have a column of true Excel dates...

=SUMPRODUCT(--(A1:A100="Primary"),--(TEXT(B1:B100,"myyyy")="42008"))

Or:

=SUMPRODUCT(--(A1:A100="Primary"),--(MONTH(B1:B100)=4),--(YEAR(B1:B100)=2008))

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I tried what you said and it doesnt seem to work. In one column i have
type
of centre (whether centre is Tertiary, secondary or Primary) and in
another
column i have operation start date (Month when operation started). If i
want
to know how many primary centres were started in April 2008 how do i go
about
doing it?

When i use SUMPRODUCT function like how you have given below, i get the
count of Primary centres but i do not get the count of primary centres
started in April 2008.

Regards
Krishnan

"Luke M" wrote:

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula in, it
wasn't actually calculating. It was just able to remember what value it
had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to recalculate
(by
adding a number) XL gave up on trying to figure it out. This is similar
to
what happenes whne you use a function from an Add-in, but don't have the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in
the
range that the countifs were counting, an #name? error was produced,
and it
couldn't be undone. Is there any way around this ? Though I'm still
impressed
that 2003 was able to keep the 2007 results to begin with. What does
xlfn
mean ?

Thanks,


Steve



.

can i link it to a cell which has Apr-2008?

Yes, just make sure you enter Apr-2008 as a true Excel date.

D1 = any true Excel date (formatted any way you'd like such as mmm-yyyy, so,
if you enter the date 4/1/2008 it will display as Apr-2008)

Then just refer to D1 in the formula like this:

=SUMPRODUCT(--(A1:A100="Primary"),--(TEXT(B1:B100,"myyyy")=TEXT(D1,"myyyy")))

Or:

=SUMPRODUCT(--(A1:A100="Primary"),--(MONTH(B1:B100)=D1),--(YEAR(B1:B100)=D1))

You can also use a cell to hold Primary and then refer to that cell the same
way.

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Thank you. Info was very useful. But can i link the period to a cell. What
i
mean is: Instead of mentioning "42008", can i link it to a cell which has
Apr-2008? This is to avoid manually changing period in very cell.

Regards
Krishnan

"T. Valko" wrote:

Assuming you have a column of true Excel dates...

=SUMPRODUCT(--(A1:A100="Primary"),--(TEXT(B1:B100,"myyyy")="42008"))

Or:

=SUMPRODUCT(--(A1:A100="Primary"),--(MONTH(B1:B100)=4),--(YEAR(B1:B100)=2008))

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I tried what you said and it doesnt seem to work. In one column i have
type
of centre (whether centre is Tertiary, secondary or Primary) and in
another
column i have operation start date (Month when operation started). If i
want
to know how many primary centres were started in April 2008 how do i go
about
doing it?

When i use SUMPRODUCT function like how you have given below, i get the
count of Primary centres but i do not get the count of primary centres
started in April 2008.

Regards
Krishnan

"Luke M" wrote:

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula in,
it
wasn't actually calculating. It was just able to remember what value
it
had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to
recalculate
(by
adding a number) XL gave up on trying to figure it out. This is
similar
to
what happenes whne you use a function from an Add-in, but don't have
the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened
in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in
the
range that the countifs were counting, an #name? error was produced,
and it
couldn't be undone. Is there any way around this ? Though I'm still
impressed
that 2003 was able to keep the 2007 results to begin with. What does
xlfn
mean ?

Thanks,


Steve



.

Hi,
I have type of centres (Primary, secondary, teritiary) in one column and
another column with 1 or 0. I want a command that says Multiply centres which
has '1' in the other column. How do i do it?


"T. Valko" wrote:

Assuming you have a column of true Excel dates...

=SUMPRODUCT(--(A1:A100="Primary"),--(TEXT(B1:B100,"myyyy")="42008"))

Or:

=SUMPRODUCT(--(A1:A100="Primary"),--(MONTH(B1:B100)=4),--(YEAR(B1:B100)=2008))

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I tried what you said and it doesnt seem to work. In one column i have
type
of centre (whether centre is Tertiary, secondary or Primary) and in
another
column i have operation start date (Month when operation started). If i
want
to know how many primary centres were started in April 2008 how do i go
about
doing it?

When i use SUMPRODUCT function like how you have given below, i get the
count of Primary centres but i do not get the count of primary centres
started in April 2008.

Regards
Krishnan

"Luke M" wrote:

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula in, it
wasn't actually calculating. It was just able to remember what value it
had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to recalculate
(by
adding a number) XL gave up on trying to figure it out. This is similar
to
what happenes whne you use a function from an Add-in, but don't have the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in
the
range that the countifs were counting, an #name? error was produced,
and it
couldn't be undone. Is there any way around this ? Though I'm still
impressed
that 2003 was able to keep the 2007 results to begin with. What does
xlfn
mean ?

Thanks,


Steve



.

Not following you on this.

?????

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I have type of centres (Primary, secondary, teritiary) in one column and
another column with 1 or 0. I want a command that says Multiply centres
which
has '1' in the other column. How do i do it?


"T. Valko" wrote:

Assuming you have a column of true Excel dates...

=SUMPRODUCT(--(A1:A100="Primary"),--(TEXT(B1:B100,"myyyy")="42008"))

Or:

=SUMPRODUCT(--(A1:A100="Primary"),--(MONTH(B1:B100)=4),--(YEAR(B1:B100)=2008))

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I tried what you said and it doesnt seem to work. In one column i have
type
of centre (whether centre is Tertiary, secondary or Primary) and in
another
column i have operation start date (Month when operation started). If i
want
to know how many primary centres were started in April 2008 how do i go
about
doing it?

When i use SUMPRODUCT function like how you have given below, i get the
count of Primary centres but i do not get the count of primary centres
started in April 2008.

Regards
Krishnan

"Luke M" wrote:

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula in,
it
wasn't actually calculating. It was just able to remember what value
it
had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to
recalculate
(by
adding a number) XL gave up on trying to figure it out. This is
similar
to
what happenes whne you use a function from an Add-in, but don't have
the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened
in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in
the
range that the countifs were counting, an #name? error was produced,
and it
couldn't be undone. Is there any way around this ? Though I'm still
impressed
that 2003 was able to keep the 2007 results to begin with. What does
xlfn
mean ?

Thanks,


Steve



.

There are 3 columns in excel. First column represents type of centre
(Primary, Secondary and teritiary). Second column represents whether the
centre is in operation or not. (if it is in operation then 1 and if it is not
in operation then it is 0). Third column represent rental deposit for each
type of centre.

So i need a formula which checks whether the centre is in operation or not
and then if it is in operation then check the type of centre and then
multiply with the rental deposit depending on type of centre.

Hope this clear. let me know if it is still not clear.

Regards
Krishnan

"T. Valko" wrote:

Not following you on this.

?????

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I have type of centres (Primary, secondary, teritiary) in one column and
another column with 1 or 0. I want a command that says Multiply centres
which
has '1' in the other column. How do i do it?


"T. Valko" wrote:

Assuming you have a column of true Excel dates...

=SUMPRODUCT(--(A1:A100="Primary"),--(TEXT(B1:B100,"myyyy")="42008"))

Or:

=SUMPRODUCT(--(A1:A100="Primary"),--(MONTH(B1:B100)=4),--(YEAR(B1:B100)=2008))

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I tried what you said and it doesnt seem to work. In one column i have
type
of centre (whether centre is Tertiary, secondary or Primary) and in
another
column i have operation start date (Month when operation started). If i
want
to know how many primary centres were started in April 2008 how do i go
about
doing it?

When i use SUMPRODUCT function like how you have given below, i get the
count of Primary centres but i do not get the count of primary centres
started in April 2008.

Regards
Krishnan

"Luke M" wrote:

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula in,
it
wasn't actually calculating. It was just able to remember what value
it
had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to
recalculate
(by
adding a number) XL gave up on trying to figure it out. This is
similar
to
what happenes whne you use a function from an Add-in, but don't have
the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened
in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in
the
range that the countifs were counting, an #name? error was produced,
and it
couldn't be undone. Is there any way around this ? Though I'm still
impressed
that 2003 was able to keep the 2007 results to begin with. What does
xlfn
mean ?

Thanks,


Steve



.



.

Try this...

A1:A10 = center type
B1:B10 = in operation = 1 or 0
C1:C10 = rental deposit

=SUMPRODUCT(--(A1:A10="primary"),B1:B10,C1:C10)

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
There are 3 columns in excel. First column represents type of centre
(Primary, Secondary and teritiary). Second column represents whether the
centre is in operation or not. (if it is in operation then 1 and if it is
not
in operation then it is 0). Third column represent rental deposit for each
type of centre.

So i need a formula which checks whether the centre is in operation or not
and then if it is in operation then check the type of centre and then
multiply with the rental deposit depending on type of centre.

Hope this clear. let me know if it is still not clear.

Regards
Krishnan

"T. Valko" wrote:

Not following you on this.

?????

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I have type of centres (Primary, secondary, teritiary) in one column
and
another column with 1 or 0. I want a command that says Multiply centres
which
has '1' in the other column. How do i do it?


"T. Valko" wrote:

Assuming you have a column of true Excel dates...

=SUMPRODUCT(--(A1:A100="Primary"),--(TEXT(B1:B100,"myyyy")="42008"))

Or:

=SUMPRODUCT(--(A1:A100="Primary"),--(MONTH(B1:B100)=4),--(YEAR(B1:B100)=2008))

--
Biff
Microsoft Excel MVP


"Krishnan" wrote in message
...
Hi,
I tried what you said and it doesnt seem to work. In one column i
have
type
of centre (whether centre is Tertiary, secondary or Primary) and in
another
column i have operation start date (Month when operation started).
If i
want
to know how many primary centres were started in April 2008 how do i
go
about
doing it?

When i use SUMPRODUCT function like how you have given below, i get
the
count of Primary centres but i do not get the count of primary
centres
started in April 2008.

Regards
Krishnan

"Luke M" wrote:

Guessing xlfn = Excel Function.

It sounds like although Excel was nice enough to leave the formula
in,
it
wasn't actually calculating. It was just able to remember what
value
it
had
calculated when you had it open in 2007.

As there is no COUNTIFS in 2003, as soon as you asked it to
recalculate
(by
adding a number) XL gave up on trying to figure it out. This is
similar
to
what happenes whne you use a function from an Add-in, but don't
have
the
add-in activated.

A recommendation would be to replace your COUNTIFS with SUMPRODUCT.
Example:
=SUMPRODUCT(((A1:A100="Tom")+(A1:A100="Bob"))*1)
would give you a count of all the Tom and Bob's in a1:a100
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When
opened
in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number
in
the
range that the countifs were counting, an #name? error was
produced,
and it
couldn't be undone. Is there any way around this ? Though I'm
still
impressed
that 2003 was able to keep the 2007 results to begin with. What
does
xlfn
mean ?

Thanks,


Steve



.



.