tezza
Active Member
- Joined
- Sep 10, 2006
- Messages
- 382
- Office Version
- 2016
- 2010
- Platform
- Windows
- Web
Hi all,
Hope you can help as my mind has gone blank.
I have one sheet that has name, dates and durations that I will filtered out as needed.
On the second sheet I need a cell to lookup how many times a specific name shows in col A and give the subtotal of the visible durations.
Mary's name exists 10 times with a duration of 1 hour each. When filtered, Mary only shows 6 times (for example) giving a total duration of 6 hours.
Normally, I would do a simple subtotal(9,........) but I need to do it for multiple visible names created from the second sheet list.
So F14 is this example changes depending on the filters in Col A:C
Hope you can help as my mind has gone blank.
I have one sheet that has name, dates and durations that I will filtered out as needed.
On the second sheet I need a cell to lookup how many times a specific name shows in col A and give the subtotal of the visible durations.
Mary's name exists 10 times with a duration of 1 hour each. When filtered, Mary only shows 6 times (for example) giving a total duration of 6 hours.
Normally, I would do a simple subtotal(9,........) but I need to do it for multiple visible names created from the second sheet list.
| Book1 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Name | Day | Duration | |||||||||
| 2 | Mary | 21-May | 1 | |||||||||
| 3 | Bob | 21-May | 1 | |||||||||
| 4 | Mary | 21-May | 1 | |||||||||
| 5 | James | 21-May | 1 | |||||||||
| 6 | Mary | 21-May | 1 | |||||||||
| 7 | Mary | 22-May | 1 | How many Mary's have been filtered | ||||||||
| 8 | Bob | 22-May | 1 | Subtotal duration of visible Mary's | ||||||||
| 9 | Mary | 22-May | 1 | |||||||||
| 10 | James | 22-May | 1 | eg: | Col B is filtered with the 21st and 22nd | |||||||
| 11 | Mary | 22-May | 1 | |||||||||
| 12 | Mary | 23-May | 1 | Result | ||||||||
| 13 | Bob | 23-May | 1 | |||||||||
| 14 | Mary | 23-May | 1 | This cell = 6 | ||||||||
| 15 | James | 23-May | 1 | |||||||||
| 16 | MAry | 23-May | 1 | |||||||||
| 17 | Mary | 24-May | 1 | |||||||||
| 18 | Bob | 24-May | 1 | |||||||||
| 19 | Mary | 24-May | 1 | |||||||||
| 20 | James | 24-May | 1 | |||||||||
| 21 | MAry | 24-May | 1 | |||||||||
| 22 | Mary | 25-May | 1 | |||||||||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B7:B22 | B7 | =B2+1 |
So F14 is this example changes depending on the filters in Col A:C
