What do you have in G2 - a literal January or something like 1-Jan-15 displayed as January?
Hello World,
I think I have a challenge here, or maybe this is an excel limitation. I need to count the unique Ticket Numbers (Column B) for a given month / date range (Column E), but also segregate it out by Place (Column D) uniquely. The table to the left is the source of the information and variables, while the table on the left with the months and place numbers are the results. I also have excel 2007.
Thank you and good luck to anyone that can help
Ticket Number Company Place Start Date End Date Place1 Place2 1 Company1 Place1 1/1/2015 1/2/2015 January 2 1 1 Company2 Place1 1/1/2015 1/2/2015 Febuary 1 1 2 Company3 Place2 1/2/2015 2/1/2015 3 Company2 Place1 1/2/2015 1/3/2015 4 Company2 Place1 2/1/2015 2/2/2015 5 Company2 Place2 2/8/2015 2/9/2015
What do you have in G2 - a literal January or something like 1-Jan-15 displayed as January?
Assuming too much and qualifying too much are two faces of the same problem.
G2 would have all the literal months written out going downward in order, January, February, March...
However, there are two "tables" here and are separated by the column that has blanks. The table to the left will have all the information needed to get the answers to the table on the right, specifically "H2, H3, I2 and I3" . Here is a link to the same thing that might help when looking at it.
http://s9.postimg.org/6em1pvxq7/unnamed.png
Last edited by micdanspe; Feb 6th, 2015 at 11:22 AM. Reason: wording
H2, control+shift+enter, not just enter, copy across, and down:
Code:=SUM(IF(FREQUENCY(IF($A$2:$A$7<>"", IF($C$2:$C$7=H$1,IF($D$2:$D$7-DAY($D$2:$D$7)+1=(1&$G2)+0, IF($E$2:$E$7-DAY($E$2:$E$7)+1=(1&$G2)+0, MATCH($A$2:$A$7,$A$2:$A$7,0))))), ROW($A$2:$A$7)-ROW($A$2)+1),1))
Assuming too much and qualifying too much are two faces of the same problem.
Like this thread? Share it with others