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?

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(--(D12:D110=C5),--(O12:O110=D5),--(B12:B110=E5),--(B12:B110<=F5))


--
Biff
Microsoft Excel MVP


"SeanO" wrote in message
...
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?

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

"SeanO" wrote in message
...
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?

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.

--
__________________________________
HTH

Bob

"Fred Smith" wrote in message
...
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

"SeanO" wrote in message
...
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?

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

Fred.

"Bob Phillips" wrote in message
...
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.

--
__________________________________
HTH

Bob

"Fred Smith" wrote in message
...
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

"SeanO" wrote in message
...
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?