# 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
Thanks
Del.

#### Attachments

• 22.8 KB Views: 9

#### 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

• 25.1 KB Views: 11
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) )}