Countifs in 2003 = _xlfn. ??

[Steve]

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

 

[Luke M]

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

 

[Shane Devenshire]

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

 

[jam]

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

 

[T. Valko]

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.

 

[Krishnan]

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

 

[T. Valko]

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

 

[Krishnan]

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]

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.

 

[Krishnan]

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]

Not following you on this.

?????

 

[Krishnan]

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]

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)