Is there a way to put a formula as the chart's dataset

[BenBouck]

Hello everyone,

I'm working on a sheet, and I have to insert a chart showing the number of items per cathegory (look the sample sheet if you want to understand).

So, of course, I'm creating a hidden sheet with a COUNTIF for each cathegory.

This solution actually works really fine, but here is my ""problem"" : if I want to add a cathegory, I will have to add a new COUNTIF, and to do so for every new cathegory I will create.

So I'm wondering if there is a way to write a function directly in the data selection field of the chart, that will reference the table "cathegory" and the data table, and automaticely count for every cathegories.

I don't want to use VBA for this, because my company doesn't allow .xlsm files on out informatic server.

If there is no way to do this, there is no real issue, but if this is possible, it would be perfect !

Thanks

[oeldere]

C3 =Countif($B$3:$B3,$B3)

After that a pivot table.

After that a pivot graph.

See the attached file.

[BenBouck]

Thank you for your answer.

It is not what I want to do, if I add a new line with another cathegory, it doesn't appear in the pivot table nor in the chart. Moreover, the pivot table needs to be saved somewhere, and I'd lie the data to be 'hidden' inside the chart directly.

I don't know if it is possible, but it would be great if there is a way to do so !! :D

[oeldere]

Did you look at my excel file?

I added new categories in the table and those new categories also in the pivot graph.

Please reply.