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
<tbody>
</tbody>
<tbody>
</tbody>
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
<tbody>
</tbody>
It contains the following formula which was kindly provided to me. I have used named ranges to make it easier for me to understand:
<tbody>
</tbody>
<tbody>
</tbody>
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.
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
A | B | C | D | |
|---|---|---|---|---|
1 | YYYY-MM-DD | hh:mm:ss | Dr | Speed |
2 | 9/04/2016 | 16:11:51 | W0 | 86 |
3 | 9/04/2016 | 16:14:44 | W0 | 92 |
4 | 9/04/2016 | 16:14:46 | W0 | 97 |
5 | 9/04/2016 | 16:14:49 | W0 | 92 |
6 | 9/04/2016 | 16:15:37 | E1 | 96 |
7 | 9/04/2016 | 16:15:50 | E1 | 97 |
8 | 9/04/2016 | 16:17:24 | W0 | 91 |
9 | 9/04/2016 | 16:18:01 | W0 | 105 |
10 | 9/04/2016 | 16:18:58 | W0 | 101 |
<tbody>
</tbody>
| Sheet: 20209 |
<tbody>
</tbody>
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
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2 | Speed | 110 | ||||||||||||||||||||||||
| 3 | Direction | E1 | ||||||||||||||||||||||||
| 4 | Type | ALL | ||||||||||||||||||||||||
| 5 | ||||||||||||||||||||||||||
| 6 | 00:00:00 | 01:00:00 | 02:00:00 | 03:00:00 | 04:00:00 | 05:00:00 | 06:00:00 | 07:00:00 | 08:00:00 | 09:00:00 | 10:00:00 | 11:00:00 | 12:00:00 | 13:00:00 | 14:00:00 | 15:00:00 | 16:00:00 | 17:00:00 | 18:00:00 | 19:00:00 | 20:00:00 | 21:00:00 | 22:00:00 | 23:00:00 | ||
| 7 | 01:00:00 | 02:00:00 | 03:00:00 | 04:00:00 | 05:00:00 | 06:00:00 | 07:00:00 | 08:00:00 | 09:00:00 | 10:00:00 | 11:00:00 | 12:00:00 | 13:00:00 | 14:00:00 | 15:00:00 | 16:00:00 | 17:00:00 | 18:00:00 | 19:00:00 | 20:00:00 | 21:00:00 | 22:00:00 | 23:00:00 | 00:00:00 | ||
| 8 | Fri | 8/04/2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
<tbody>
</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:
Worksheet Formulas
<tbody> </tbody> |
<tbody>
</tbody>
Workbook Defined Names
<tbody> </tbody> |
<tbody>
</tbody>
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.
