| Home |
| Search |
| Today's Posts |
|
#1
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
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:D10+E2:E10)) With November 1st 2008 in C14 try =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy"),--(C2:C10="Motor"),(D2:D10+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
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:D10+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:D10+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
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:D10+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:D10+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
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:D10+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:D10+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
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:D10+E2:E10)) With November 1st 2008 in C14 try =SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy"),--(C2:C10="Motor"),(D2:D10+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
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:D10+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:D10+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
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:D20000+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:D10+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:D10+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
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:D20000+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:D10+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:D10+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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| PLS HLP! Countifs Syntax Problem? | Excel Worksheet Functions | |||
| Delete syntax problem | Excel Worksheet Functions | |||
| SendKeys syntax problem | New Users to Excel | |||
| .XValues syntax problem | Charts and Charting in Excel | |||
| Syntax problem | Excel Worksheet Functions | |||
Linear Mode
