• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Monthly totals

derek davis

New Member
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.
 

Attachments

Hui

Excel Ninja
Staff member
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

New Member
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

Excel Ninja
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
 

Attachments

Last edited:

derek davis

New Member
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

Well-Known Member
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) )}
 
Top