Log In
Register
Home
Power Pivot
Advanced Excel Formulas
August 23, 2019
Offline
I have a table of data that I have attached a slicer to, I would like to count the values in a column when the slicer changes
The column to count has 3 value, G, Y, R
When I select the person on the slicer, I would like the totals to change from the overall total in these Values within the field to those of the individual
Example:
Total 45G | 17Y | 66R
PersonA 12G | 1Y | 12R
I cannot seem to get this to work and any help would be appreciated. I cannot build a pivot table as some value are blank with screws up another formula and the property show blanks as does not remove the work blank. I cannot add a space to the blank cells as that messes with the formula as well ...
any help would be greatly appreciated.
VIP
Trusted Members
December 7, 2016
Offline
VIP
Trusted Members
June 25, 2016
Offline
Hi Lisa
You will need to create 3 helper columns G, Y and R.
In the helper columns you can check the Color column to see if it is G,Y or R and indicate 1 or 0. (refer example below)
Only then you can use the SUBTOTAL function as mentioned by Anders.
| Color | G | Y | R |
| R | 0 | 0 | 1 |
| G | 1 | 0 | 0 |
| G | 1 | 0 | 0 |
| Y | 0 | 1 | 0 |
| Y | 0 | 1 | 0 |
| G | 1 | 0 | 0 |
| R | 0 | 0 | 1 |
| Subtotal | 3 | 2 | 2 |
Hope this helps.
Sunny
1 Guest(s)
