# How to set criteria range from date in multiple cells?

I have something like this: I need to sum up all values for user, if date is is some range.

If there is no need to check dates, then I can use this formula in excel:

``````=SUMIFS(ValuesColumn;UsersColumn;User)
``````

But how is it posible to read date criteria too? Something like this?

``````=SUMIFS(ValuesColumn;UsersColumn;User;DATE(YearColumn;MonthColumn;DayColumn);">="&StartDate)
``````
• Your code looks correct (except for the semicolons instead of commas). What result does this give you? – Jonno Dec 29 '15 at 10:32
• Excel drop a error "We found a problem with this formula. Try clicking Insert Function... " – Serbin Dec 29 '15 at 10:36
• It's likely because you're using DATE(YearColumn;MonthColumn;DayColumn). Are you able to put this on your page in another column somewhere, and use this as your criteria? – Jonno Dec 29 '15 at 10:44
• Yes, this is a solution, but this solution is ugly. But this works too. – Serbin Dec 29 '15 at 10:50
• Since you have the date broken up into separate columns, Excel does not see it as a date. Therefore, date functions likely will not work on those columns. Can you clarify "I need to sum up all values for user, if date is is some range". What is "some range"? Now look at your SUMIFS, SUMIFS uses the following syntax `SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`. If you look at your formulas, they are not using the proper syntax. Also, in the last formula, where does the "&StartDate" come from? – CharlieRB Dec 29 '15 at 14:26

For illustration, I placed the data you have above in columns `A:E`, then created a `SUMIFS` formula to give me the sum of the values in `E` for each user and for a range in the day column. You did not clarify how or where you were going to put the formula. So I took liberty to lay it out for example purposes with headers for Start Day, End Day and each user.

The Start and End Day fields are to give the formula a range. Then the following formula is entered in the cell below `User1` in `I2`.

With `SUMIFS` you must give it a range to sum if the criterion are met. So here is the formula I used.

=SUMIFS(\$E\$2:\$E\$7,\$D\$2:\$D\$7,I1,\$A\$2:\$A\$7,"2015",\$B\$2:\$B\$7,"10",\$C\$2:\$C\$7, ">=" &\$G\$2, \$C\$2:\$C\$7, "<=" &\$H\$2)

Here it is in Excel. Lets break it down starting after `(`;

• `\$E\$2:\$E\$7` gives the formula the column to sum values from if the criteria is met;
• `\$D\$2:\$D\$7,I1,` is the first criteria looking for a matches in the range to the value in `I1`;
• `\$A\$2:\$A\$7,"2015",\$B\$2:\$B\$7,"10",` is looking to match the first two columns to `2015` and `10`. Since there are no other variations, I just typed them in;
• `\$C\$2:\$C\$7, ">=" &\$G\$2, \$C\$2:\$C\$7, "<=" &\$H\$2)` looks for values in the range greater or equal to the value in `G2` for the beginning of the range and less than or equal to the value in `H2` for the end of the range.

In summary, the formula is looking for values to sum column `E` where `User1` is in column `D` AND `2015` in column `A` AND `10` in column `B` AND values in column `C` that are equal to or between the values in `G2` and `H2`.

I copied the formula across for the other users and it calculates those easily because of the use of absolute references for the data.

Reference