Zero Amounts in Category Tracker

Microsoft Excel
1

If Category tracker is designed to track actual data, why does it show categories with ZERO amounts? (unused categories) How do I NOT show zero amounts?

2

Hi @tjones4852 - If you don’t want to display the categories that have zero in the Amount column, you could consider a slight edit to the formulas in row 13, columns A, B, and C.

I use a checkbox in cell C11 just above the Amount column to turn them on and off. I did that because I like to be able to go back to the original output if necessary, but you could just edit the formula and skip the checkbox - both variations are provided below.

In cell C11, I did menu path Insert–>Checkbox.
Then I deleted the original formulas in A13, B13 and C13.
Then I put a new formula in A13 as follows:

=LET(output,CHOOSE({1,2,3}, SORTBY(L$3#,$N$3#,1,$M$3#,1,$L$3#,1), SORTBY(N3#&" / "&M$3#,$N$3#,1,$M$3#,1,$L$3#,1), SORTBY(O$3#,$N$3#,1,$M$3#,1,$L$3#,1)),IF($C$11=TRUE,FILTER(output, SORTBY(O$3#,$N$3#,1,$M$3#,1,$L$3#,1)<>0),output))

This incorporates the original 3 formulas into one and adds a FILTER to drop those with zero in the Amount column.

If you don’t want to use a checkbox, you can use this formula instead:

=LET(output,CHOOSE({1,2,3}, SORTBY(L$3#,$N$3#,1,$M$3#,1,$L$3#,1), SORTBY(N3#&" / "&M$3#,$N$3#,1,$M$3#,1,$L$3#,1), SORTBY(O$3#,$N$3#,1,$M$3#,1,$L$3#,1)),FILTER(output, SORTBY(O$3#,$N$3#,1,$M$3#,1,$L$3#,1)<>0))

Mine looks something like this:

CleanShot 2024-11-04 at 14.45.44

1 Like
3

Thanks for the quick response.

This did not work and here are my results.

From Developer, I inserted form control checkbox, dragged across C11, then edit text to Hide Zero Amounts.
I then
Right-clicked on the checkbox and selected Format Control.
In the Format Control dialog box, I went to the Control tab.
In the Cell link field, I typed C11
I deleted the original A13-C13 and copied your first formula into A13. All data appeared.

1 Like
4

I suspect that it is not working because our implementation of the checkbox is different. I am using Excel for Mac which doesn’t appear to have Form Controls available. So it is hard for me to test your variant. One idea to try is remove the “=TRUE” from “$C$11=TRUE”. You can also try the 2nd formula which doesn’t depend on a checkbox.

1 Like
5

I went with plan B. Thanks for your help

2 Likes
6

OK I used the Checkbox method and it worked.
Now how to remove zero amounts in the Pie Chart in the center of the Category Tracker sheet?

1 Like
7

Hi @shwarm - You could temporary move the chart to expose cell E9 and edit it with the formula below to filter out the zero amounts from the chart.

For the checkbox version, with a checkbox value in cell C11, replace cell E9 formula with:

=IF(C11,
IF(E6="Category",
  INDEX(SORTBY(FILTER(L3#,(N3#=F6)*(O3#<>0)),FILTER(O3#,(N3#=F6)*(O3#<>0)),-1),SEQUENCE(MIN(G6,COUNTIFS(N3#,$F$6,O3#,"<>0")))),
  SORTBY(FILTER(Q3#,R3#<>0),FILTER(R3#,R3#<>0),-1)
),
IF(E6="Category",
  INDEX(SORTBY(FILTER(L3#,N3#=F6),FILTER(O3#,N3#=F6),-1),SEQUENCE(MIN(G6,COUNTIF(N3#,$F$6)))),
  SORTBY(Q3#,R3#,-1)))

For the non-checkbox version, replace cell E9 with:

=IF(E6="Category",
  INDEX(SORTBY(FILTER(L3#,(N3#=F6)*(O3#<>0)),FILTER(O3#,(N3#=F6)*(O3#<>0)),-1),SEQUENCE(MIN(G6,COUNTIFS(N3#,$F$6,O3#,"<>0")))),
  SORTBY(FILTER(Q3#,R3#<>0),FILTER(R3#,R3#<>0),-1)
)
2 Likes
8

HA. That worked. I would never have come up with that myself.
Thank you,
Bob

4 Likes