Totals for a date period
Hello,
Im am trying to calculate the total amounts for each month, into one summarised number, like this:
Can anybody help, its most likely very easy, I just dont know how to select by month.
Many thanks.
Best regards...Jan
Hello,
Im am trying to calculate the total amounts for each month, into one summarised number, like this:
Can anybody help, its most likely very easy, I just dont know how to select by month.
Many thanks.
Best regards...Jan
One way using a formula:
The formula in B2, filled or copied down the column:
=SUMIFS(Table 1::B,Table 1::A,">="&$A2,Table 1::A,"<="&EDATE($A2,1))
This assumes the month names in column A are actually Date & Time, which you can confirm by clicking on and month name and looking lower left.
Substitute ; for , in the formula if your regions uses , as a decimal separator.
Another way, which doesn't require formula, is to use the built-in Categories functionality.
Click the "gear" in a group row and choose 'Subtotal'
Result:
When you're done turn off or remove the category in the panel at the right:
SG
One way using a formula:
The formula in B2, filled or copied down the column:
=SUMIFS(Table 1::B,Table 1::A,">="&$A2,Table 1::A,"<="&EDATE($A2,1))
This assumes the month names in column A are actually Date & Time, which you can confirm by clicking on and month name and looking lower left.
Substitute ; for , in the formula if your regions uses , as a decimal separator.
Another way, which doesn't require formula, is to use the built-in Categories functionality.
Click the "gear" in a group row and choose 'Subtotal'
Result:
When you're done turn off or remove the category in the panel at the right:
SG
Hi Jan,
The SUMIF function is your friend. But first we need to give a month name for SUMIF to find.
Extra column B in Table 1. You can hide this column when all is working well.
Formula in Table 1 B2 =MONTHNAME(MONTH($A2))
Fill down.
Formula in Table 2 B2 =SUMIF(Table 1::B,$A2,Table 1::C)
Fill down.
Regards,
Ian.
Thank you, that was just what I needed!!!!
Best...Jan
Thank you this was also very helpfull!!!
Best...Jan
Totals for a date period