Counts / Frequencies / Multiple Criteria’s Problem

micdanspe

New Member
Joined
Feb 5, 2015
Messages
3
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>
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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:
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top