# How to calculate the statistics at the end of each month?

Discussion in 'Business Applications' started by Saqib_Ah, Oct 22, 2012.

Not open for further replies.

Joined:
Oct 22, 2012
Messages:
2
Hello!

I am working in an educational system. Due to transfers and withdrawals of students my strength list varies at the end of every month. Is there any way to record the month end statistics in excel?
Like I want to record what is the strength of particular section at the end of Aug, Sep and so on ... If there is any excel formula that I can use to automatically record at the end of every month.

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,964
First Name:
Wayne
you may need to provide more information on what you want to achieve and how its recorded in excel - perhaps some dummy data in a spreadsheet uploaded - showing what you achieve now and how you want to change the statistic ?

3. ### Slots

Joined:
Oct 21, 2012
Messages:
62
Saqib_Ah,

etaf is absolutely correct. There is no way for any of us to guess the answer to your question without more information. Assuming your "Strength List" is a number, and you have that number calculated for every day, it is possible to get a subtotal by date, or if you have each month listed on a separate tab, it is possible to have the total for each month show up on a separate year to date tab by month. But there simply isn't any way to guess - we have to have more information.

Joined:
Oct 22, 2012
Messages:
2
ok. I have attached a sample data.Hope it will work for you.

File size:
13.6 KB
Views:
45
5. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,964
First Name:
Wayne
you can use a countif
=COUNTIF(\$E\$33:\$E\$39,"Transfer In")
=COUNTIF(\$E\$33:\$E\$39,"Transfer Out")
=COUNTIF(\$E\$33:\$E\$39,"Withdrawel")

this formulas just counts the range for the criteria countif(Range,Criteria)
for the full list

and

then for the month summary you could use
=COUNTIFS(\$E\$55:\$E\$61,"Transfer In",\$D\$55:\$D\$61,">=01/08/2012",\$D\$55:\$D\$61,"<=31/08/2012")
=COUNTIFS(\$E\$55:\$E\$61,"Transfer Out",\$D\$55:\$D\$61,">=01/08/2012",\$D\$55:\$D\$61,"<=31/08/2012")
=COUNTIFS(\$E\$55:\$E\$61,"Withdrawel",\$D\$55:\$D\$61,">=01/08/2012",\$D\$55:\$D\$61,"<=31/08/2012")

this formulas counts the range for the criteria status transfer etc , but then has two additional criteria for the Dates

countifs(Range1,Criteria1,Range3,Criteria2,Range3,Criteria3)*

BUT the dates need to be in date format-which I have changed

i have attached the modified sheet - called ETAF and highlighted yellow where i have added the formulas

i have not done to much work as I'm not sure of the layout and what may change

File size:
19.5 KB
Views:
87

As Seen On