+ Reply to Thread
Results 1 to 13 of 13

Monthly Dept counts

  1. #1
    Registered User
    Join Date
    08-04-2015
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    82

    Monthly Dept counts

    Hii Guys,

    Need a formula in which I can auto calculate monthly department wise counts, for e.g Dept AAA - Apr (Month) - 8 (Per Day)* 6 (Working Weekdays) = 48 like this i need for all the department
    Attached Files Attached Files
    You can add reputation(s) of those who helped

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Monthly Dept counts

    Try this in F20:

    =SUMPRODUCT((TEXT($D$5:$V$5,"mmm")=F$19)*OFFSET($D$6:$V$6,MATCH($E20,$C$7:$C$11,0),))*6

    Copy across and down

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Monthly Dept counts

    Put this in F20, drag down and across

    =SUMPRODUCT(($E20=$C$7:$C$11)*(MONTH(1&F$19)=MONTH($D$5:$V$5))*$D$7:$V$11*$D$4:$V$4)
    Cheers!
    Deep Dave

  4. #4
    Registered User
    Join Date
    08-04-2015
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    82

    Re: Monthly Dept counts

    Hii Guys,

    Thanks for Your help..but need some corrections , AAA dept Apr month is 48 but May month will be like this (8*6)+ (4*6)+(4*6)+(3*6)+(3*2)=120 but both the formulas calculate total has 132.

  5. #5
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Monthly Dept counts

    How is the last one (3*2)? :O

    Can you tell me the cell references?

  6. #6
    Registered User
    Join Date
    08-04-2015
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    82

    Re: Monthly Dept counts

    Column I6 (30-MAY).. it goes like this...30th May, 31st may (End of May month), (June starts from here)01st June, 02nd June 03rd June 04th June 05th June so I7 will be multiplied by only 2 days for the month of may not by 6days, the formula is multiplying by 6days

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Monthly Dept counts

    re-arange the data so that the table is in a verticaly way.

    after that use a pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  8. #8
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Monthly Dept counts

    Quote Originally Posted by sam1105 View Post
    Column I6 (30-MAY).. it goes like this...30th May, 31st may (End of May month), (June starts from here)01st June, 02nd June 03rd June 04th June 05th June so I7 will be multiplied by only 2 days for the month of may not by 6days, the formula is multiplying by 6days
    I am not clear.. :O

  9. #9
    Registered User
    Join Date
    08-04-2015
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    82

    Re: Monthly Dept counts

    Didnt understood about rearranging data.. can u pls explain the same in attachment...Thanks

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Monthly Dept counts

    f20
    Please Login or Register  to view this content.
    TRY THIS AND COPY ACROSS
    Note:- Please change June as Jun and July as Jul
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Monthly Dept counts

    with a pivot table.

    see the attached file.

  12. #12
    Registered User
    Join Date
    08-04-2015
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    82

    Re: Monthly Dept counts

    It doesnt count as per month...i need counts as per month & department wise

  13. #13
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Monthly Dept counts

    Quote Originally Posted by sam1105 View Post
    It doesnt count as per month...i need counts as per month & department wise

    See the attached file
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need a table w/ employees, dept., and counts
    By wmedina83 in forum Excel General
    Replies: 5
    Last Post: 04-29-2014, 10:21 PM
  2. Finding Total For Individuals By Monthly Tab and Unit Counts
    By mydragonstalents in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2013, 02:25 PM
  3. [SOLVED] Criteria Based Monthly Counts
    By simplyxl in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-17-2013, 09:16 AM
  4. Bottom Values for any given dept
    By coda1395 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 07-09-2013, 12:47 PM
  5. [SOLVED] comparing name and dept and then show job title
    By [email protected] in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-21-2012, 10:59 PM
  6. Replies: 1
    Last Post: 07-11-2008, 05:57 AM
  7. [SOLVED] Find name in list and get dept # enter dept # on work sheet
    By Jamba in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-30-2006, 06:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1