Count "All" occurrences of a category from a drop down list

C

Chilly2011

New Member
Joined
May 29, 2011
Messages
6
Good morning

I have previously received some great assistance for a problem I had using the Countifs function from Joris, a board regular. I wish to expand on this formula to be able to achieve a new level of functionality for a project I'm working on.

I currently have the below data on one sheet:

Excel 2013 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD="bgcolor: #5B9BD5"]
YYYY-MM-DD​
[/TD]
[TD="bgcolor: #5B9BD5"]
hh:mm:ss​
[/TD]
[TD="bgcolor: #5B9BD5"]
Dr​
[/TD]
[TD="bgcolor: #5B9BD5"]
Speed​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD="bgcolor: #DDEBF7"]
9/04/2016​
[/TD]
[TD="bgcolor: #DDEBF7"]
16:11:51​
[/TD]
[TD="bgcolor: #DDEBF7"]
W0​
[/TD]
[TD="bgcolor: #DDEBF7"]
86​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD]
9/04/2016​
[/TD]
[TD]
16:14:44​
[/TD]
[TD]
W0​
[/TD]
[TD]
92​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD="bgcolor: #DDEBF7"]
9/04/2016​
[/TD]
[TD="bgcolor: #DDEBF7"]
16:14:46​
[/TD]
[TD="bgcolor: #DDEBF7"]
W0​
[/TD]
[TD="bgcolor: #DDEBF7"]
97​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD]
9/04/2016​
[/TD]
[TD]
16:14:49​
[/TD]
[TD]
W0​
[/TD]
[TD]
92​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD="bgcolor: #DDEBF7"]
9/04/2016​
[/TD]
[TD="bgcolor: #DDEBF7"]
16:15:37​
[/TD]
[TD="bgcolor: #DDEBF7"]
E1​
[/TD]
[TD="bgcolor: #DDEBF7"]
96​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7​
[/TD]
[TD]
9/04/2016​
[/TD]
[TD]
16:15:50​
[/TD]
[TD]
E1​
[/TD]
[TD]
97​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8​
[/TD]
[TD="bgcolor: #DDEBF7"]
9/04/2016​
[/TD]
[TD="bgcolor: #DDEBF7"]
16:17:24​
[/TD]
[TD="bgcolor: #DDEBF7"]
W0​
[/TD]
[TD="bgcolor: #DDEBF7"]
91​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
9​
[/TD]
[TD]
9/04/2016​
[/TD]
[TD]
16:18:01​
[/TD]
[TD]
W0​
[/TD]
[TD]
105​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
10​
[/TD]
[TD="bgcolor: #DDEBF7"]
9/04/2016​
[/TD]
[TD="bgcolor: #DDEBF7"]
16:18:58​
[/TD]
[TD="bgcolor: #DDEBF7"]
W0​
[/TD]
[TD="bgcolor: #DDEBF7"]
101​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: 20209[/TD]
[/TR]
</tbody>[/TABLE]

On another sheet I have a summary table which aims at identifying trends by using a drop down box (located in cells B2, B3 and B4 below) to select items and conditionally format the table contents using the Countifs formula. This is a condensed version of the original table:

Excel 2013 32 bit
ABCDEFGHIJKLMNOPQRSTUVWXYZ

<tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Speed[/TD]
[TD="bgcolor: #FFFFFF, align: center"]110[/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Direction[/TD]
[TD="bgcolor: #FFFFFF, align: center"]E1[/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Type[/TD]
[TD="bgcolor: #FFFFFF, align: center"]ALL[/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]
[TD="bgcolor: #BDD7EE, align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]
[TD="bgcolor: #BDD7EE, align: center"]00:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]01:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]02:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]03:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]04:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]05:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]06:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]07:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]08:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]09:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]10:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]11:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]12:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]13:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]14:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]15:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]16:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]17:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]18:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]19:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]20:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]21:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]22:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]23:00:00[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]
[TD="bgcolor: #BDD7EE, align: center"]01:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]02:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]03:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]04:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]05:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]06:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]07:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]08:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]09:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]10:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]11:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]12:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]13:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]14:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]15:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]16:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]17:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]18:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]19:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]20:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]21:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]22:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]23:00:00[/TD]
[TD="bgcolor: #BDD7EE, align: center"]00:00:00[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Fri[/TD]
[TD="bgcolor: #BDD7EE, align: center"]8/04/2016[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]
[TD="bgcolor: #BDD7EE, align: center"]0[/TD]

</tbody>
Time Chart 20209​

It contains the following formula which was kindly provided to me. I have used named ranges to make it easier for me to understand:



[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,"<="&'Time Chart 20209'!C$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!D$6,Time20209,"<="&'Time Chart 20209'!D$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!E$6,Time20209,"<="&'Time Chart 20209'!E$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!F$6,Time20209,"<="&'Time Chart 20209'!F$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!G$6,Time20209,"<="&'Time Chart 20209'!G$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!H$6,Time20209,"<="&'Time Chart 20209'!H$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!I$6,Time20209,"<="&'Time Chart 20209'!I$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!J$6,Time20209,"<="&'Time Chart 20209'!J$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!K$6,Time20209,"<="&'Time Chart 20209'!K$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]L8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!L$6,Time20209,"<="&'Time Chart 20209'!L$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]M8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!M$6,Time20209,"<="&'Time Chart 20209'!M$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!N$6,Time20209,"<="&'Time Chart 20209'!N$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]O8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!O$6,Time20209,"<="&'Time Chart 20209'!O$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]P8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!P$6,Time20209,"<="&'Time Chart 20209'!P$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Q8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!Q$6,Time20209,"<="&'Time Chart 20209'!Q$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]R8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!R$6,Time20209,"<="&'Time Chart 20209'!R$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]S8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!S$6,Time20209,"<="&'Time Chart 20209'!S$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]T8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!T$6,Time20209,"<="&'Time Chart 20209'!T$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]U8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!U$6,Time20209,"<="&'Time Chart 20209'!U$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]V8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!V$6,Time20209,"<="&'Time Chart 20209'!V$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]W8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!W$6,Time20209,"<="&'Time Chart 20209'!W$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]X8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!X$6,Time20209,"<="&'Time Chart 20209'!X$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Y8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!Y$6,Time20209,"<="&'Time Chart 20209'!Y$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Z8[/TH]
[TD="align: left"]=COUNTIFS(Speed20209,">="&'Time Chart 20209'!$B$2,Direction20209,'Time Chart 20209'!$B$3,Date20209,'Time Chart 20209'!$B8,Time20209,">="&'Time Chart 20209'!Z$6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Date20209[/TH]
[TD="align: left"]='20209'!$A$2:$A$7112[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Direction20209[/TH]
[TD="align: left"]='20209'!$C$2:$C$7112[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Speed20209[/TH]
[TD="align: left"]='20209'!$D$2:$D$7112[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Time20209[/TH]
[TD="align: left"]='20209'!$B$2:$B$7112[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

My goal is to be able to Count All categories of each drop down list when the choice, "Both" or similar word is selected. For example, in Cell B3 (Direction) I currently only have the option of selecting either "W1" or "E0". I want to include the word "Both" in the drop down list and then have it count both "W1" and "E0" together as a total. As can be seen the criteria has a combination of letters and numbers.

This one has me stumped.

Thanks in advance.
 
You must log in or register to reply here.

Similar threads

V
  • Question Question
time and date fix
Replies
7
Views
187
vmjan02
V
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
535
dshtesy
D
O
  • Solved
Bracketing Issue
Replies
9
Views
132
Peter_SSs
Peter_SSs
C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
284
CaptainGravyBum
C
B
  • Question Question
Chart help - rainfall data
Replies
2
Views
469
JV0710
J

Share this page

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