AND function as a criteria in SUMIFS function

[DashboardNovice]

I have the following formula:

=SUMIFS('Vendor History'!$S:$S,'Vendor History'!$Q:$Q,$A2,'Vendor History'!$R:$R,AND(">="&EOMONTH(C$1,-1)+1,"<="&C$1))

The criteria range in column R consists of a list of dates. For my criteria, I want Excel to choose dates in column R that are between the first day of the month AND the last day of the month. The first and last day of the month is determined by C1.

I can't figure out what is wrong with my syntax. Any ideas?

Edit: I have modified my formula to:

=SUMIFS('Vendor History'!$S:$S,'Vendor History'!$Q:$Q,$A2,'Vendor History'!$R:$R,AND(">="&EOMONTH(C$1,-1)+1, "<="&EOMONTH(C$1,0)))

and still nothing occurs. When I use formula evaluator (F9) on the AND function, it shows a #VALUE error.

 

[NARAYANK991]

Hi ,

You should not use the AND function for this ; the technique to be used when you want multiple criteria on one column is to repeat the column , and have the criteria on their own ; thus , in this specific case , the formula would be :

=SUMIFS('Vendor History'!$S:$S,'Vendor History'!$Q:$Q,$A2,'Vendor History'!$R:$R,">=" & EOMONTH(C$1,-1)+1,'Vendor History'!$R:$R, "<=" & EOMONTH(C$1,0)))

Narayan

 

[Somendra Misra]

Hi,

Just to add a function, you can use like below construct:

=SUMPRODUCT(SUMIF($E$4:$E$13,ROW(INDIRECT(DATE(YEAR($C$1),MONTH($C$1),1)&":"&EOMONTH($C$1,0))),F4:F13))

Here Column E is a date column, C1 has date according to which the start & end date of that month is decided. and F column has values.

Regards,

 

[Hui]

=SUMPRODUCT(('Vendor History'!$S:$S)*('Vendor History'!$Q:$Q=$A2)*('Vendor History'!$R:$R>=EOMONTH(C$1,-1)+1)*('Vendor History'!$R:$R<=EOMONTH(C$1,0)))

 

[Hui]

Regardless of which solution above you choose to go with, I strongly recommend using references like Q:Q

This can force Excel to look at every row in the column all 1048576 rows
This is often the source of slow / sluggish workbooks

Even if you have 20,000 rows you are better to use Q1:Q20000, which is 1/50th the size of Q:Q

 

[NARAYANK991]

Hi ,

Please see this for more information :

http://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx

Narayan