get sum based on current month

[k1dr0ck]

hi,

starting at A6 i have series of dates(sequence of months)
at B6 i have amounts

how do you get the sum of amounts in column B based on current month
add the amount next to the current month down to the last row with data
then show the sum in B3
and show the last date in the row of column A in B4

and make the sum in B3 and the date in B4 to auto update if i add series of date-amount in column A-B

also in row 3
how do you also get the sum of amounts next the cell value "BALANCE" and show the sum in B1 sheet "BALANCE-TOTAL CA"
how do you also get the sum of amounts next the cell value "TOTAL CA" and show the sum in B2 sheet "BALANCE-TOTAL CA"

attached sample file

[Rev12]

May be this could help you as please see on the attached Excel.

copy of the formula on B3

=2000*(COUNTA(A6:A40)-MATCH(B1,A6:A6:A40,0)+1)

copy of the formula on B4

=LARGE(A6:A40,1)

[JohnTopley]

To SUM up to and including month in B1

in B3

=SUMIF($A$6:$A$1000,"<=" &$B$1,$B$6:$B$1000)

[k1dr0ck]

@REV thanks for the reply im actually dealing with different amounts, thanks for the large formula

@JOHNTOPLEY thanks for the reply
the comparison should be ">=" right?
also the sum is less than the actual sum i think it does not include the current month only the succeeding months

[FDibbins]

No, the < = is correct, but you did not specify when you need the sum to start from?

[k1dr0ck]

the sum starts at the month-year in column A that is the same as the current month

i tried to use the <= the shown sum is not correct
when i use the >= the shown sum is less than 1 month than the correct sum

No, the < = is correct, but you did not specify when you need the sum to start from?

[k1dr0ck]

so i had an idea to subtract a month from the current date to get the correct sum

A2 is TODAY()
B2 is DATE(YEAR(A2), MONTH(A2) - $C$2, DAY(A2))
C2 is 1

then B3 is SUMIF($A$6:$A$1000,">=" &$B$2,$B$6:$B$1000)

it now shows the correct sum on B3

my problem now is when i copy-paste the formula of B3 to say F3
it does not auto update the columns to SUMIF($E$6:$E$1000,">=" &$F$2,$F$6:$F$1000)

is there a way to auto update the columns when you paste the formula?

[JohnTopley]

The reason you are one month short is you are using TODAY() as the start date so 01/06/2018 in column A is less than TODAY (4/06/2018) so will be excluded.

So either test for month /year OR change dates in A to be month-end dates e.g. 28/02/2017

and use

=SUMIF($A$6:$A$1000,">=" &INT($B$1),$B$6:$B$1000)

See attached where dates have been changed.

[JohnTopley]

To "auto" update

=SUMIF($A$6:$A$1000,">=" &INT(B$1),$B$6:$B$1000)

[k1dr0ck]

hi,

thanks for the reply
how do you auto update the other columns
=SUMIF($A$6:$A$1000,">=" &INT(B$1),$B$6:$B$1000)

when i paste the formula to F3 it should auto update to
SUMIF($E$6:$E$1000,">=" &INT(F$1),$F$6:$F$1000)

[JohnTopley]

in B3

=SUMIF(A$6:A$1000,">=" &INT(B$1),B$6:B$1000)


Copy /paste to F3

[k1dr0ck]

got it! thanks alot!