another sumifs plea
 

I'm struggling to convert a sumifs line from 2007 to excel 2003.
The line I have working correctly in 2007 is:

=SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"=
0.00",'Washing MC Rental'!$L$4:$L$21,"28/2/2010")-SUMIFS('Washing MC
Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC
Rental'!$L$4:$L$21,"31/3/2010")
Can any one please help me to put this into 2003?
Best Regards Dave

another sumifs plea
 

=SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,
--('Washing MC Rental'!$Q$4:$Q$21,=0),
--('Washing MC
Rental'!$L$4:$L$21,=--"2010-03-01"),
--('Washing MC
Rental'!$L$4:$L$21,<=--"2010-02-31"))

--

HTH

Bob

"Dave W" wrote in message
...
I'm struggling to convert a sumifs line from 2007 to excel 2003.
The line I have working correctly in 2007 is:

=SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"=
0.00",'Washing MC Rental'!$L$4:$L$21,"28/2/2010")-SUMIFS('Washing MC
Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC
Rental'!$L$4:$L$21,"31/3/2010")
Can any one please help me to put this into 2003?
Best Regards Dave

another sumifs plea
 

Hi,
I don't think SUMIFS is available in 2003, try

=sumproduct(--('Washing MC Rental'!$Q$4:$Q$21=0.00,--('Washing MC
Rental'!$L$4:$L$21"28/2/2010"),'Washing MC
Rental'!$K$4:$K$21-Sumproduct(--('Washing MC
Rental'!$Q$4:$Q$21=0.00),--('Washing MC
Rental'!$L$4:$L$21"31/3/2010"),'Washing MC Rental'!$K$4:$K$21)

"Dave W" wrote:

I'm struggling to convert a sumifs line from 2007 to excel 2003.
The line I have working correctly in 2007 is:

=SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"=
0.00",'Washing MC Rental'!$L$4:$L$21,"28/2/2010")-SUMIFS('Washing MC
Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC
Rental'!$L$4:$L$21,"31/3/2010")
Can any one please help me to put this into 2003?
Best Regards Dave

another sumifs plea
 

2003 does not have sumifs function

you need to use sumproduct to do it try:

=sumproduct(--('Washing MC Rental'!$Q$4:$Q$21=0,00)*('Washing MC
Rental'!$L$4:$L$21date(2010,02,28)),('Washing MC
Rental'!$K$4:$K$21)-sumproduct(--('Washing MC Rental'!$Q$4:$Q$21=0)*('Washing
MC Rental'!$L$4:$L$21=date(2010,03,31)),('Washing MC Rental'!$K$4:$K$21))


hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Dave W" escreveu:

I'm struggling to convert a sumifs line from 2007 to excel 2003.
The line I have working correctly in 2007 is:

=SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"=
0.00",'Washing MC Rental'!$L$4:$L$21,"28/2/2010")-SUMIFS('Washing MC
Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC
Rental'!$L$4:$L$21,"31/3/2010")
Can any one please help me to put this into 2003?
Best Regards Dave

another sumifs plea
 

Try this...

=SUMPRODUCT(--(TEXT(L4:L21,"m/yyyy")="3/2010"),--(Q4:Q21<""),--(Q4:Q21=0),K4:K21)

Add your sheet name.

--
Biff
Microsoft Excel MVP


"Dave W" wrote in message
...
I'm struggling to convert a sumifs line from 2007 to excel 2003.
The line I have working correctly in 2007 is:

=SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"=
0.00",'Washing MC Rental'!$L$4:$L$21,"28/2/2010")-SUMIFS('Washing MC
Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC
Rental'!$L$4:$L$21,"31/3/2010")
Can any one please help me to put this into 2003?
Best Regards Dave

Re Try This
 

Thank you very much worked perfectly. Best Regards Dave



T. Valko wrote:

Try this...
30-Mar-10

Try this...

=SUMPRODUCT(--(TEXT(L4:L21,"m/yyyy")="3/2010"),--(Q4:Q21<""),--(Q4:Q21=0),K4:K21)

Add your sheet name.

--
Biff
Microsoft Excel MVP

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Making Silverlight Emulate Synchronous Requests
http://www.eggheadcafe.com/tutorials...ht-emulat.aspx

Re Try This
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


<David Williamson wrote in message
...
Thank you very much worked perfectly. Best Regards Dave



T. Valko wrote:

Try this...
30-Mar-10

Try this...

=SUMPRODUCT(--(TEXT(L4:L21,"m/yyyy")="3/2010"),--(Q4:Q21<""),--(Q4:Q21=0),K4:K21)

Add your sheet name.

--
Biff
Microsoft Excel MVP

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Making Silverlight Emulate Synchronous Requests
http://www.eggheadcafe.com/tutorials...ht-emulat.aspx