Sum total for same month and auto arrangement.

**elricsia** · 06-25-2015, 04:54 AM

Hi all,

I need some help on my excel formula, please find attachment for my sample.

1. A1 is the month of record show.
2. I will lock my table Monthly purchase (K3:AA35) and Payment (K41:AA57)
3. Daily In/Out is for my whole year record.

My problem is:
a) i need some improvement for my Payment - Total (L42:L56), which formula to sum total for the month (Example: A1 = Jun-15 then will total up all the Jun purchase).
b) since i will lock up my payment table, i need some formula to automatic look up for all the negative (paid) as N42:Z56.

sorry for my English and please let me know if you dont understand. Thank you.

**elricsia** · 06-25-2015, 05:39 AM

Any one can help me? I have totally no idea how to settle it.

**JohnTopley** · 06-25-2015, 05:48 AM

Try for June purchase in L42

=SUMPRODUCT(--(MONTH(Table1[Column1])=MONTH($A$1))*(Table1[Company]=$K42)*(Table1[Amount]>0)*Table1[Amount])

I you want monthly sum of negatives ...

=SUMPRODUCT(--(MONTH(Table1[Column1])=MONTH($A$1))*(Table1[Company]=$K42)*(Table1[Amount]<0)*Table1[Amount])