Counts / Frequencies / Multiple Criteria’s Problem

micdanspe

New Member
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 NumberCompanyPlaceStart DateEnd DatePlace1Place2
1Company1Place11/1/2015 1/2/2015January21
1Company2Place11/1/2015 1/2/2015Febuary11
2Company3Place21/2/2015 2/1/2015
3Company2Place11/2/2015 1/3/2015
4Company2Place12/1/20152/2/2015
5Company2Place22/8/20152/9/2015

<tbody>
</tbody>
 

micdanspe

New Member
What do you have in G2 - a literal January or something like 1-Jan-15 displayed as January?
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:

Aladin Akyurek

MrExcel MVP
H2, control+shift+enter, not just enter, copy across, and down:
Rich (BB 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))
 

micdanspe

New Member
H2, control+shift+enter, not just enter, copy across, and down:
Rich (BB 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))
Of course your name is Aladin, because this was magic!
Thank you so much!
 

Some videos you may like

This Week's Hot Topics

Top