Conditional Excel Sum based on dates range

excel worksheet-function conditional-operator sumifs
Alex picture Alex · Dec 17, 2014 · Viewed 12.3k times · Source

I have got a spreadsheet in where dates are in column B and values are in column E.

Dates are with the format: dd/mm/yy Values are with the format: XXX,XX€

I am trying to sum cells based on dates but I can't.

I am using the function SUMIFS like this to calculate the values from November:

=SUMIFS(E9:E1000;B9:B1000;">="DATE(2014;11;1)&"<="DATE(2014,11,30))

I am doing something wrong but I don't know what.

Answer

Alexander Bell picture Alexander Bell · Dec 17, 2014

In order to get the conditional Sum for specified date range (Nov 2014), correct your syntax as shown in the sample below:

=SUMIFS(E9:E1000,B9:B1000,">="&DATE(2014,11,1), B9:B1000,"<"&DATE(2014,12,1))

Note: this solution is pertinent to the US date format: mm/dd/yyyy, so you may need to correct it per your case depends on how DATE() function is set on your PC. Also, due to my regional settings it's using a comma-separator, which may be different from the one on you PC (you may need to use semicolon ;).

Hope this will help. Kind regards,

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More