Pivot table Count by Closed Date

distillerjunkie

New member
Joined
Mar 16, 2016
Messages
33
Reaction score
0
Points
0
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?
 
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).
 
I have a Pivot Table that counts closed dates.
Point me to which one does this.




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?





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?




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.
 
Point me to which one does this.
PivotClosedDates.PNG



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.
 
Back
Top