Formulas new in excel 2007 not compatible with 97-03

[SeanO]

I have been utilising excel 2007 and in specific the COUNTIFS function. I
have completed the formula but when I have given it to the person to use,
they only have excel 97-03. I have installed the compatibility conversion
file but the formula does not work and all that appears is "#NAME?". It seems
the COUNTIFS formula can not be used in previous excel versions.

Question 1, what formula can I use in previous excel versions that does the
dame as the COUNTIFS? Below is the forumla that I am using?

COUNTIFS($D$12:$D$110,C5,$O$12:$O$110,"Y",$B$12:$B$110,">=39630",$B$12:$B$110,"<=39660")

Question 2, the criteria range specifying >=39630 & <=39660 is in relations
to a date range. Instead of using the numbers how can I input the actualy
date range in the formula?

 

[T. Valko]

Use the SUMPRODUCT function. It'll work in both versions.

Question 2, the criteria range specifying >=39630 & <=39660
is in relations to a date range. Instead of using the numbers how
can I input the actualy date range in the formula?

It's better to use cells to hold the criteria. You're already using C5 for
one of those criteria. Just use cells for all of the criteria:

C5 = whatever C5 equals!
D5 = Y
E5 = 7/1/2008
F5 = 7/31/2008

=SUMPRODUCT(--(D12110=C5),--(O12:O110=D5),--(B12:B110>=E5),--(B12:B110<=F5))

 

[Fred Smith]

Sumproduct is the equivalent of Countifs. Try:
=SUMPRODUCT(($D$12:$D$110=C5)*($O$12:$O$110="Y")*($B$12:$B$110>=39630)*($B$12:$B$110<=39660)

Use the Date function to specify dates rather than numbers, as in:
=SUMPRODUCT(($D$12:$D$110=C5)*($O$12:$O$110="Y")*($B$12:$B$110>=DATE(2008,7,1))*($B$12:$B$110<=DATE(2008,7,31)))

If you want an entire month in a year, you can use:
=SUMPRODUCT(($D$12:$D$110=C5)*($O$12:$O$110="Y")*(MONTH($B$12:$B$110)=7)*(YEAR($B$12:$B$110)=2008))

Regards,
Fred

 

[Bob Phillips]

Fred,

Not to nit-pick, but SP is NOT the equivalent to COUNTIFS. It can be
stretched to perform multiple condition tests as COUNTIFS can, but COUNTIFS
will be more efficient, but SP can also be stretched to do things that
COUNTIFS cannot even come close to. And of course, it also SUMs PRODUCTs
(quite efficiently) <bg>

I must applaud your not continuing the OPs deplorable date testing ( >=39630
& <=39660 ) though, unambiguous dates are better all round.

 

[Fred Smith]

Good points, Bob. What I meant to say was "Sumproduct will do what you want
in 97-03"

Fred.