RicardoCubed
Board Regular
- Joined
- Jul 10, 2013
- Messages
- 206
- Office Version
- 365
- Platform
- Windows
Hi. I am looking to be able to when filtering a list on Sheet2 which is created/populated by using index/match which looks to a database contained on Sheet1 to count/sum up TEXT results ignoring hidden rows which correctly are filtered out.
Numbers work but text I am having a problem with - even when I tried the aggregate formula (which i suspect will work but I am not constructing formula correctly).
I hope the below illustrates what i would like to solve for. I filter the list in Sheet2 by department by selecting accounting in cell B4, and in cell C1 the then filtered count should be 2. Countif formula doesn't work as it doesn't ignore filtered/hidden rows in the data set. The result using aggregate in cell D1 works correctly so and I thought i could employ that for text.
Help appreciated. Thanks in advance.
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>
Numbers work but text I am having a problem with - even when I tried the aggregate formula (which i suspect will work but I am not constructing formula correctly).
I hope the below illustrates what i would like to solve for. I filter the list in Sheet2 by department by selecting accounting in cell B4, and in cell C1 the then filtered count should be 2. Countif formula doesn't work as it doesn't ignore filtered/hidden rows in the data set. The result using aggregate in cell D1 works correctly so and I thought i could employ that for text.
Help appreciated. Thanks in advance.
A | B | C | D | |
---|---|---|---|---|
1 | Total of Promo | 4 | 7800 | |
2 | ||||
3 | Name | Depart | Promo | Amount |
4 | name1 | accounting | x | 2,000.00 |
5 | name2 | development | 0 | - |
6 | name3 | tax | x | 1,500.00 |
7 | name4 | accounting | 0 | - |
8 | name5 | tax | 0 | - |
9 | name6 | accounting | x | 3,000.00 |
10 | name7 | corporate | 0 | - |
11 | name8 | development | 0 | - |
12 | name9 | hr | x | 1,300.00 |
13 | name10 | corporate | 0 | - |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
Worksheet Formulas
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>