Problem with Syntax?

[MurrayBarn]

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:C10,"=B16") that I have in
C16 is giving me a #VALUE error. Is my logic or my Syntax wrong?

 

[Jacob Skaria]

Try the below formula which will return the total for November..

=SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D210+E2:E10))


With November 1st 2008 in C14 try
=SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")=TEXT(C14,"mmyyyy"),--(C2:C10="Motor"),(D210+E2:E10))


If this post helps click Yes

 

[Jacob Skaria]

Try the below which will return total for November 2008

=SUMPRODUCT(--(TEXT(B2:B10,"mmyyyy")="112008"),--(C2:C10="Motor"),(D210+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"),(D210+E2:E10))


If this post helps click Yes

 

[Jacob Skaria]

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"),(D210+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"),(D210+E2:E10))


If this post helps click Yes

 

[Jacob Skaria]

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"),(D210+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"),(D210+E2:E10))



If this post helps click Yes

 

[MurrayBarn]

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

 

[MurrayBarn]

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]

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"),(D220000+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

 

[MurrayBarn]

Thanks - you have been very helpful. Have a great weekend!