|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 12th, 2009, 08:38 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Syntax?
2 B C D E
3 PolicyStartDate Class ClassTotal VAT 4 01/11/2008 Motor 10,039.24 1,232.89 5 01/11/2008 Motor -200.00 -24.56 6 01/11/2008 Motor 1,150.60 141.30 7 01/11/2008 Non-Motor 12,414.70 1,524.61 8 01/12/2008 Non-Motor 12,414.70 1,524.61 9 01/12/2008 SASRIA 52.75 6.48 10 01/12/2008 SASRIA 52.75 6.48 11 12 13 November December 14 Month Beg 01/11/2008 01/12/2008 15 Month End 30/11/2008 31/12/2008 16 Motor #VALUE! 17 Non-Motor I am trying to Sum columns D:E for all data that falls in the month of November 2008, then Decmber 2008, Jan 2009 etc that corresponds to the definition Motor as per column C but my formula =SUMIFS(D4:E10,B4:B10,"=C14",B4:B10,"=C15",C4:C1 0,"=B16") that I have in C16 is giving me a #VALUE error. Is my logic or my Syntax wrong? 
|
|
#2
June 12th, 2009, 10:13 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with Syntax?
Try the below formula which will return the total for November..
=SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D2  10+E2:E10))With November 1st 2008 in C14 try =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy"),--(C2:C10="Motor"),(D2 10+E2:E10))If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: 2 B C D E 3 PolicyStartDate Class ClassTotal VAT 4 01/11/2008 Motor 10,039.24 1,232.89 5 01/11/2008 Motor -200.00 -24.56 6 01/11/2008 Motor 1,150.60 141.30 7 01/11/2008 Non-Motor 12,414.70 1,524.61 8 01/12/2008 Non-Motor 12,414.70 1,524.61 9 01/12/2008 SASRIA 52.75 6.48 10 01/12/2008 SASRIA 52.75 6.48 11 12 13 November December 14 Month Beg 01/11/2008 01/12/2008 15 Month End 30/11/2008 31/12/2008 16 Motor #VALUE! 17 Non-Motor I am trying to Sum columns D:E for all data that falls in the month of November 2008, then Decmber 2008, Jan 2009 etc that corresponds to the definition Motor as per column C but my formula =SUMIFS(D4:E10,B4:B10,"=C14",B4:B10,"=C15",C4:C1 0,"=B16") that I have in C16 is giving me a #VALUE error. Is my logic or my Syntax wrong?
|
|
#3
June 12th, 2009, 10:20 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with Syntax?
Try the below which will return total for November 2008
=SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D2 10+E2:E10))If you want to refer cell C14 with month beginning date; then try the below. Month end date is not needed. =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy"),--(C2:C10="Motor"),(D2 10+E2:E10))If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: 2 B C D E 3 PolicyStartDate Class ClassTotal VAT 4 01/11/2008 Motor 10,039.24 1,232.89 5 01/11/2008 Motor -200.00 -24.56 6 01/11/2008 Motor 1,150.60 141.30 7 01/11/2008 Non-Motor 12,414.70 1,524.61 8 01/12/2008 Non-Motor 12,414.70 1,524.61 9 01/12/2008 SASRIA 52.75 6.48 10 01/12/2008 SASRIA 52.75 6.48 11 12 13 November December 14 Month Beg 01/11/2008 01/12/2008 15 Month End 30/11/2008 31/12/2008 16 Motor #VALUE! 17 Non-Motor I am trying to Sum columns D:E for all data that falls in the month of November 2008, then Decmber 2008, Jan 2009 etc that corresponds to the definition Motor as per column C but my formula =SUMIFS(D4:E10,B4:B10,"=C14",B4:B10,"=C15",C4:C1 0,"=B16") that I have in C16 is giving me a #VALUE error. Is my logic or my Syntax wrong?
|
|
#4
June 12th, 2009, 10:25 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with Syntax?
Syntax error!! in the previous posts..
Try the below which will return total for November 2008 =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D2 10+E2:E10))If you want to refer cell C14 with month beginning date; then try the below. Month end date is not needed. =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy")),--(C2:C10="Motor"),(D2 10+E2:E10))If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: 2 B C D E 3 PolicyStartDate Class ClassTotal VAT 4 01/11/2008 Motor 10,039.24 1,232.89 5 01/11/2008 Motor -200.00 -24.56 6 01/11/2008 Motor 1,150.60 141.30 7 01/11/2008 Non-Motor 12,414.70 1,524.61 8 01/12/2008 Non-Motor 12,414.70 1,524.61 9 01/12/2008 SASRIA 52.75 6.48 10 01/12/2008 SASRIA 52.75 6.48 11 12 13 November December 14 Month Beg 01/11/2008 01/12/2008 15 Month End 30/11/2008 31/12/2008 16 Motor #VALUE! 17 Non-Motor I am trying to Sum columns D:E for all data that falls in the month of November 2008, then Decmber 2008, Jan 2009 etc that corresponds to the definition Motor as per column C but my formula =SUMIFS(D4:E10,B4:B10,"=C14",B4:B10,"=C15",C4:C1 0,"=B16") that I have in C16 is giving me a #VALUE error. Is my logic or my Syntax wrong?
|
|
#5
June 12th, 2009, 11:11 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with Syntax?
Not sure if this is going to be duplicated. I think the earlier post is
having a syntax error. Try the below which will return total for November 2008 =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D2 10+E2:E10))If you want to refer cell C14 with month beginning date; then try the below. Month end date is not needed. =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy")),--(C2:C10="Motor"),(D2 10+E2:E10))If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: 2 B C D E 3 PolicyStartDate Class ClassTotal VAT 4 01/11/2008 Motor 10,039.24 1,232.89 5 01/11/2008 Motor -200.00 -24.56 6 01/11/2008 Motor 1,150.60 141.30 7 01/11/2008 Non-Motor 12,414.70 1,524.61 8 01/12/2008 Non-Motor 12,414.70 1,524.61 9 01/12/2008 SASRIA 52.75 6.48 10 01/12/2008 SASRIA 52.75 6.48 11 12 13 November December 14 Month Beg 01/11/2008 01/12/2008 15 Month End 30/11/2008 31/12/2008 16 Motor #VALUE! 17 Non-Motor I am trying to Sum columns D:E for all data that falls in the month of November 2008, then Decmber 2008, Jan 2009 etc that corresponds to the definition Motor as per column C but my formula =SUMIFS(D4:E10,B4:B10,"=C14",B4:B10,"=C15",C4:C1 0,"=B16") that I have in C16 is giving me a #VALUE error. Is my logic or my Syntax wrong?
|
|
#6
June 12th, 2009, 11:53 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with Syntax?
Thanks Jacob - that formula works well but I want to push it to the next
level. Firstly, I read somewhere that SUMPRODUCT uses a lot of resources. I will need a couple of hundred SUMPRODUCT formulas per workbook, there are 30 workbooks per annum and I can have up to 60 workbooks open at the same time. Will SUMIFS work and if so, does it use as much resource as SUMPRODUCT Secondly, I tried to change the ranges into columns ie B2:B10 becomes B:B but the formula returned a #VALUE error. Is there a way around it other than making the range B2:B20000? Regards "Jacob Skaria" wrote: Try the below formula which will return the total for November.. =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D2 10+E2:E10))With November 1st 2008 in C14 try =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy"),--(C2:C10="Motor"),(D2 10+E2:E10))If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: 2 B C D E 3 PolicyStartDate Class ClassTotal VAT 4 01/11/2008 Motor 10,039.24 1,232.89 5 01/11/2008 Motor -200.00 -24.56 6 01/11/2008 Motor 1,150.60 141.30 7 01/11/2008 Non-Motor 12,414.70 1,524.61 8 01/12/2008 Non-Motor 12,414.70 1,524.61 9 01/12/2008 SASRIA 52.75 6.48 10 01/12/2008 SASRIA 52.75 6.48 11 12 13 November December 14 Month Beg 01/11/2008 01/12/2008 15 Month End 30/11/2008 31/12/2008 16 Motor #VALUE! 17 Non-Motor I am trying to Sum columns D:E for all data that falls in the month of November 2008, then Decmber 2008, Jan 2009 etc that corresponds to the definition Motor as per column C but my formula =SUMIFS(D4:E10,B4:B10,"=C14",B4:B10,"=C15",C4:C1 0,"=B16") that I have in C16 is giving me a #VALUE error. Is my logic or my Syntax wrong?
|
|
#7
June 12th, 2009, 01:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with Syntax?
Hi Jacob
Seems like there was a duplication issue. Would you be so kind as to have a look at my second post on this thread. Regards Murray "Jacob Skaria" wrote: Not sure if this is going to be duplicated. I think the earlier post is having a syntax error. Try the below which will return total for November 2008 =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D2 10+E2:E10))If you want to refer cell C14 with month beginning date; then try the below. Month end date is not needed. =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy")),--(C2:C10="Motor"),(D2 10+E2:E10))If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: 2 B C D E 3 PolicyStartDate Class ClassTotal VAT 4 01/11/2008 Motor 10,039.24 1,232.89 5 01/11/2008 Motor -200.00 -24.56 6 01/11/2008 Motor 1,150.60 141.30 7 01/11/2008 Non-Motor 12,414.70 1,524.61 8 01/12/2008 Non-Motor 12,414.70 1,524.61 9 01/12/2008 SASRIA 52.75 6.48 10 01/12/2008 SASRIA 52.75 6.48 11 12 13 November December 14 Month Beg 01/11/2008 01/12/2008 15 Month End 30/11/2008 31/12/2008 16 Motor #VALUE! 17 Non-Motor I am trying to Sum columns D:E for all data that falls in the month of November 2008, then Decmber 2008, Jan 2009 etc that corresponds to the definition Motor as per column C but my formula =SUMIFS(D4:E10,B4:B10,"=C14",B4:B10,"=C15",C4:C1 0,"=B16") that I have in C16 is giving me a #VALUE error. Is my logic or my Syntax wrong?
|
|
#8
June 12th, 2009, 01:40 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with Syntax?
Oops. Sorry for the duplication...There was an formula error in my original
post and hence I was trying to post the corrected one...but was returning error...... To change that to 20000 is fine...only thing is you need to make sure you change all references...as below =SUMPRODUCT(--(TEXT(B2:B20000,"mmyyyy")="012008"),--(C2:C20000="Motor"),(D2 20000+E2:E20000))I am not using Excel 2007 right now; and so will not be in a position to comment on SUMIFS()..Sorry. If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: Hi Jacob Seems like there was a duplication issue. Would you be so kind as to have a look at my second post on this thread. Regards Murray "Jacob Skaria" wrote: Not sure if this is going to be duplicated. I think the earlier post is having a syntax error. Try the below which will return total for November 2008 =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D2 10+E2:E10))If you want to refer cell C14 with month beginning date; then try the below. Month end date is not needed. =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy")),--(C2:C10="Motor"),(D2 10+E2:E10))If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: 2 B C D E 3 PolicyStartDate Class ClassTotal VAT 4 01/11/2008 Motor 10,039.24 1,232.89 5 01/11/2008 Motor -200.00 -24.56 6 01/11/2008 Motor 1,150.60 141.30 7 01/11/2008 Non-Motor 12,414.70 1,524.61 8 01/12/2008 Non-Motor 12,414.70 1,524.61 9 01/12/2008 SASRIA 52.75 6.48 10 01/12/2008 SASRIA 52.75 6.48 11 12 13 November December 14 Month Beg 01/11/2008 01/12/2008 15 Month End 30/11/2008 31/12/2008 16 Motor #VALUE! 17 Non-Motor I am trying to Sum columns D:E for all data that falls in the month of November 2008, then Decmber 2008, Jan 2009 etc that corresponds to the definition Motor as per column C but my formula =SUMIFS(D4:E10,B4:B10,"=C14",B4:B10,"=C15",C4:C1 0,"=B16") that I have in C16 is giving me a #VALUE error. Is my logic or my Syntax wrong?
|
|
#9
June 12th, 2009, 01:45 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with Syntax?
Thanks - you have been very helpful. Have a great weekend!
"Jacob Skaria" wrote: Oops. Sorry for the duplication...There was an formula error in my original post and hence I was trying to post the corrected one...but was returning error...... To change that to 20000 is fine...only thing is you need to make sure you change all references...as below =SUMPRODUCT(--(TEXT(B2:B20000,"mmyyyy")="012008"),--(C2:C20000="Motor"),(D2 20000+E2:E20000))I am not using Excel 2007 right now; and so will not be in a position to comment on SUMIFS()..Sorry. If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: Hi Jacob Seems like there was a duplication issue. Would you be so kind as to have a look at my second post on this thread. Regards Murray "Jacob Skaria" wrote: Not sure if this is going to be duplicated. I think the earlier post is having a syntax error. Try the below which will return total for November 2008 =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D2 10+E2:E10))If you want to refer cell C14 with month beginning date; then try the below. Month end date is not needed. =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy")),--(C2:C10="Motor"),(D2 10+E2:E10))If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: 2 B C D E 3 PolicyStartDate Class ClassTotal VAT 4 01/11/2008 Motor 10,039.24 1,232.89 5 01/11/2008 Motor -200.00 -24.56 6 01/11/2008 Motor 1,150.60 141.30 7 01/11/2008 Non-Motor 12,414.70 1,524.61 8 01/12/2008 Non-Motor 12,414.70 1,524.61 9 01/12/2008 SASRIA 52.75 6.48 10 01/12/2008 SASRIA 52.75 6.48 11 12 13 November December 14 Month Beg 01/11/2008 01/12/2008 15 Month End 30/11/2008 31/12/2008 16 Motor #VALUE! 17 Non-Motor I am trying to Sum columns D:E for all data that falls in the month of November 2008, then Decmber 2008, Jan 2009 etc that corresponds to the definition Motor as per column C but my formula =SUMIFS(D4:E10,B4:B10,"=C14",B4:B10,"=C15",C4:C1 0,"=B16") that I have in C16 is giving me a #VALUE error. Is my logic or my Syntax wrong?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
