1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

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

Thread Status:
Not open for further replies.
Advertisement
  1. Saqib_Ah

    Saqib_Ah Thread Starter

    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. etaf

    etaf Moderator

    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

    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.
     
  4. Saqib_Ah

    Saqib_Ah Thread Starter

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

    Attached Files:

  5. etaf

    etaf Moderator

    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,"New Admission")
    =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,"New Admission",$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
     

    Attached Files:

  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - calculate statistics each
  1. FernJones
    Replies:
    2
    Views:
    209
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1073610

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice