another sumifs plea

[Dave W]

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

 

[Bob Phillips]

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

 

[Eduardo]

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)

 

[Marcelo]

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$21>date(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:

 

[T. Valko]

Try this...

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

Add your sheet name.

 

[David Williamson]

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

-
Bif
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/tutorial...1-c5c717c9b184/making-silverlight-emulat.aspx

 

[T. Valko]

You're welcome. Thanks for the feedback!