| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need help creating a score sheet with weighted values. Help Plea | Excel Worksheet Functions | |||
| Entering multiple barcode using Talbar program, having issue plea | Excel Worksheet Functions | |||
| Making two different date formats compatible - help requested plea | Excel Discussion (Misc queries) | |||
| How can I replace zeros with blank spaces during calculations plea | Excel Worksheet Functions | |||
| the autosum do not working propely ##### THAT WHAT APPEARS PLEA. | Excel Discussion (Misc queries) | |||
Linear Mode
