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.
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
thank you both so much
I ended up using a combo of sumproduct, offset and subtotal to work with the slicer ...
