Monthly totals

[derek davis]

Could I ask for some help and steer me in the right direction. I am looking to acheive three results all in Column "O"

1. create a monthly total.
2. create monthly positive result.
3. create monthly negative result.

I have tried using SUMIFS and trying to include EOMONTH functions. I would like to capture the monthly results using funtions. I have included a development sheet for your perusal please see cells O 7,8,9
Can anybody help me please.
Thanks
Del.

 

[Hui]

Derek

Firstly, Welcome to the Chandoo.org Forums

Have a try of these
O7: =SUMPRODUCT(($E$7:$E$69>=DATEVALUE("1"&N$7&$N$6))*($E$7:$E$69<=EOMONTH(DATEVALUE(1&N$7&$N$6),0))*$K$7:$K$69)
O8: =SUMPRODUCT(($E$7:$E$69>=DATEVALUE("1"&N$7&$N$6))*($E$7:$E$69<=EOMONTH(DATEVALUE(1&N$7&$N$6),0))*($K$7:$K$69>0)*($K$7:$K$69))
O9: =SUMPRODUCT(($E$7:$E$69>=DATEVALUE("1"&N$7&$N$6))*($E$7:$E$69<=EOMONTH(DATEVALUE(1&N$7&$N$6),0))*($K$7:$K$69<0)*($K$7:$K$69))

 

[derek davis]

Hi Hui, thanks for your quick response to my question. On testing all three formulas I noticed that when I enter a new line of data in columns A-G row 70 onwards that the data is not included in the final tally. I can see why this has occurred as it stops at row 69, and I will put my hand up as I didn't mention that rows 70.71,72 etc will be populated as the days & weeks go forward. I Tried to edit your formula for columns E and K to read $E7:$E, $K7:$K. but this indicated an error (Value). Is there a workaround to this problem? as I will be building on this sheet for at least 3 years. All other elemnts are working fine.

O7: SUMPRODUCT(($E$7:$E$70>=DATEVALUE("1"&N$22&$N$6))*($E$7:$E$70<=EOMONTH(DATEVALUE(1&N$22&$N$6),0))*$K$7:$K$70)

 

[bosco_yip]

Or, try this 1 formula solution instead of 3 formulas, the result is same as Hui's and which also consider your data increment problem.

In O7, copied down :

=IF(N7="","",SUMIFS(K:K,K:K,IF(LEFT(N7)="P",">0",IF(LEFT(N7)="L","<0","<>")),E:E,">="&LOOKUP(9^9,0+(N$7:N7&N$6)),E:E,"<="&EOMONTH(LOOKUP(9^9,0+(N$7:N7&N$6)),0)))

Regards
Bosco

 

[derek davis]

Thanks to Hui and Bosco for your views, they were diverse but aceived almost the same result. I appreciate your time to assist me.

Thank you.

 

[Peter Bartholomew]

With dynamic ranges for the data and numbers for the months
{= SUMIFS( totals,
totals, {"<>";">0";"<0"},
dates, ">"&DATE(yyyy,mm,0),
dates, "<"&DATE(yyyy,mm+1,1) )}