Ask Your Question
0

SUMIF + group by

asked 2017-03-30 22:58:27 +0100

Deever gravatar image

Hi folks!

In a Calc sheet, I'd like to sum all daily incomes grouped by month

image description

image description

The formula should depend on the (MM/YY formatted) "Month" column. Why not just use a manual "SUM(...)" formula? Because I'd like to still be able to sort the source table and add rows to the result table by extension with the mouse. SUMIF would do the job if I added a helper column C with something like "=DATE(YEAR(A), MONTH(A))" but that's not so elegant, obviously.

edit retag flag offensive close merge delete

Comments

Please attach or link-in examples in the respective odf file format (.ods in case of Calc) wherever applicable. Images are only useful if the question is about unexpected/faulty views of a document, dialog, ...

Lupp gravatar imageLupp ( 2017-03-31 12:28:41 +0100 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2017-03-30 23:53:07 +0100

karolus gravatar image

Hallo

Create a →Pivottable from the source with Dates in Rowfields and SumIncome in Datafield.

click into any Date-cell in Pivot-output and →→Data→Group……→by Month

edit flag offensive delete link more
0

answered 2017-03-30 23:00:27 +0100

Deever gravatar image

And here come the links to the images:

Source table: http://imgur.com/j3ZkahT Result table: http://imgur.com/KJQQ51h

edit flag offensive delete link more
0

answered 2017-03-31 13:10:52 +0100

Lupp gravatar image

In case of needing (?) immediate updates this kind of task can also be done based on formulas instead of the Pivot-table-tool. See this attached demo.

edit flag offensive delete link more

Comments

Wow, thank you so much! Already used SUMPRODUCT for weighted averages but didn't know that it can apply here, too!

Deever gravatar imageDeever ( 2017-03-31 19:46:51 +0100 )edit

I many cases SUMIF or SUMIFS can also do it. I just prefer SUMPRODUCT for tasks of the kind for some not so obvious reasons.

Lupp gravatar imageLupp ( 2017-03-31 21:21:54 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-03-30 22:58:27 +0100

Seen: 284 times

Last updated: Mar 31 '17