2

I have something like this:

Snapshot

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)
Improve this question
5
  • Your code looks correct (except for the semicolons instead of commas). What result does this give you?
    – Jonno
    Dec 29, 2015 at 10:32
  • Excel drop a error "We found a problem with this formula. Try clicking Insert Function... "
    – Serbin
    Dec 29, 2015 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, 2015 at 10:44
  • Yes, this is a solution, but this solution is ugly. But this works too.
    – Serbin
    Dec 29, 2015 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, 2015 at 14:26

1 Answer 1

Reset to default
0

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.

enter image description here

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .