Results 1 to 5 of 5

Thread: Pivot table Count by Closed Date

  1. #1

    Pivot table Count by Closed Date



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a Pivot Table that counts closed dates. I count them by month so I change it monthly to sort by that month. I need a formula for the exact same thing but for open dates for both Investigating and Implementing fields in a column in the current month (closed in Column N). I know I can't use a pivot table for this and I already have formula's that I think I can build off of, for example, =SUM(COUNTIFS('HIT Cards 2016'!G:G,{"Investigating","Implementing"},'HIT Cards 2016'!H:H,{"LCHSB"})) or =SUM(COUNTIFS('HIT Cards 2016'!B:B,"<="&(TODAY()-90),'HIT Cards 2016'!G:G,{"Investigating","Implementing"},'HIT Cards 2016'!H:H,{"Rotors/Stators"})). I just can't figure out the right combination to put the function to count the month that actually works. Can anybody help please???


    This website won't let me upload my spreadsheet because it says it exceeds this site's allowable limit. Any thoughts?

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,693
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by distillerjunkie View Post
    This website won't let me upload my spreadsheet because it says it exceeds this site's allowable limit. Any thoughts?
    One or both of:
    Reduce the size of the workbook by removing unnecessary sheets, or by reducing the volume of data within the necessary sheets.
    Uploading you file to a file sharing site and provide a link to it here (least preferred option).

  3. #3
    I could still not upload the file, deleting the extra worksheet so I create a link: https://www.dropbox.com/s/j57aki4jb2...orum.xlsm?dl=0

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,693
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by distillerjunkie View Post
    I have a Pivot Table that counts closed dates.
    Point me to which one does this.




    Quote Originally Posted by distillerjunkie View Post
    I count them by month so I change it monthly to sort by that month. I need a formula for the exact same thing but for open dates for both Investigating and Implementing fields in a column in the current month (closed in Column N).
    Where should these formula be?





    Quote Originally Posted by distillerjunkie View Post
    and I already have formula's that I think I can build off of, for example, =SUM(COUNTIFS('HIT Cards 2016'!G:G,{"Investigating","Implementing"},'HIT Cards 2016'!H:H,{"LCHSB"})) or =SUM(COUNTIFS('HIT Cards 2016'!B:B,"<="&(TODAY()-90),'HIT Cards 2016'!G:G,{"Investigating","Implementing"},'HIT Cards 2016'!H:H,{"Rotors/Stators"})).
    Where are these formulae?




    Quote Originally Posted by distillerjunkie View Post
    I know I can't use a pivot table for this
    You might yet be able to use a pivot - I see you've had a go at adding a new calculated field in the pivot table source data (column O) - we can probably build on that, although we may not need to; we can group Investigating and Implementing together in a pivot table.




    Your source table may need normalising; you have a trailing space in cell H205, and perhaps there are some dates missing in N304, N348 and N630? There may be more.

  5. #5

    Question

    Quote Originally Posted by p45cal View Post
    Point me to which one does this.
    Click image for larger version. 

Name:	PivotClosedDates.PNG 
Views:	11 
Size:	7.1 KB 
ID:	6078



    Where should these formula be?

    I was hoping to add it under the [Pivot] table that counts the Total Closed for Month



    Where are these formulae?

    These formula's don't really matter because they will not be changed, just modified in a different table?


    You might yet be able to use a pivot - I see you've had a go at adding a new calculated field in the pivot table source data (column O) - we can probably build on that, although we may not need to; we can group Investigating and Implementing together in a pivot table.




    Your source table may need normalising; you have a trailing space in cell H205, and perhaps there are some dates missing in N304, N348 and N630? There may be more.
    I do not see any of these missing dates in those cells. What is a trailing space? Does this matter for my formula's? I'm afraid I will mess the whole thing up if I change anything in the table for these formula's/pivot tables.

Tags for this Thread

Posting Permissions

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