using pie charts

[davidmor]

hi all, i hhave a sheet where im compiling data on near misses that data is then seperated into 2 pie charts one for department and the other for category, is there anyway i can have a pie chart or any chart display the info category info per department.

 

[davidmor]

This is what i currently have, but want to be able to fiter by location and have it display the % of each category

 

[Taul]

Hi,
Welcome to the board.
There are a few things missing from your screenshot so I have made a few assumptions
1-Assume the top left cell in your screenshot is cell A1
2-Your 1st Pie chart is reading from F4:G12
3-Cell G4 has a formula, guessing it is something like =COUNTIF($D$2:$D$33,$F4)
4-Unknown length of the range, my example goes down to Row 33 (adjust to suit)

If the above are correct, create a validation drop down list in cell J3 listing all the Locations.

Change the formula in cell G4 to: -

 =COUNTIFS($D$2:$D$33,$F4,$B$2:$B$33,$J$3)

And copy down

Hope this helps
Paul.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Location	Info	Category
2	01/07/2020	Cutting	stuff written here	Environmental						Location
3	02/07/2020	Finishing Cell	stuff written here	Slip/Trip/Fall		Category	Occurrence			Finishing Cell
4	02/07/2020	Flatwork	stuff written here	Fire Hazard		Chemical Control	0
5	06/07/2020	Cutting	stuff written here	Personnel/PPE		Environmental	1
6	02/07/2020	Finishing Cell	stuff written here	Machinary/Equipment		Fire Hazard	1
7	02/07/2020	Flatwork	stuff written here	Process Proceedure		Housekeeping	1
8	13/07/2020	Cutting	stuff written here	Housekeeping		Machinary/Equipment	2
9	02/07/2020	Finishing Cell	stuff written here	Environmental		Personnel/PPE	1
10	02/07/2020	Flatwork	stuff written here	Slip/Trip/Fall		Process Proceedure	1
11	06/07/2020	Cutting	stuff written here	Fire Hazard		Slip/Trip/Fall	2
12	02/07/2020	Finishing Cell	stuff written here	Personnel/PPE		Vehicles/Transport	0
13	02/07/2020	Flatwork	stuff written here	Machinary/Equipment
Sheet1

Cell Formulas
Range	Formula
G4:G12	G4	=COUNTIFS($D$2:$D$33,$F4,$B$2:$B$33,$J$3)

Cells with Data Validation
Cell	Allow	Criteria
J3	List	=location

 

[davidmor]

Thats great works excellent, thanks loads

 

[Taul]

You're welcome.

Just to add:-
If you include the word "All" into the data validation list of locations, you can use the following formula (to cell G4 and copy down), this would give you all the values you had before.

=IF($J$3="All",COUNTIF($D$2:$D$33,$F4),COUNTIFS($D$2:$D$33,$F4,$B$2:$B$33,$J$3))

 

[davidmor]

Hi Taul, dont suppose i could bother you again, would oi be able to do the same but for multiple sheets,

so have the pie chart able to select the same data by area but based on a few different sheets, i.e all the months listed across the bottom of the sheet

thanks

 

[Taul]

Hi,
I'm not sure that I understand what you need.
I assume you still have dates in column A, your screenshot is missing that column.

Question 1 - Will the data on the other month sheets be similar to the July sheet in terms of layout, albeit different numbers?
Question 2 - Are you asking to have the pie chart on the July sheet read the data from the other months so it can be displayed on the July sheet?

If it is working for July, you could copy the formula over to the other sheets. Actually it is probably easier to right click on the July page tab and select "Move or Copy", tick the little box to "Create a copy" and then paste the new month data to the new sheet. That way, all the month sheets will have a consistent layout.

If you want to stay on the July sheet (or a new front sheet) and just select the month in a drop down, that can be done but the easier option would be to put the years worth of data on the same page, so the pie chart is only reading from one source rather than trying to read from each separate month page.

 

[davidmor]

Hi Taul, i have the date on each month, but would like a collective date for all months combined on a seperate sheet, i have overvivew tabs at the start but they only show the total per department and the total of each type, i would like to be able to have the pie chart work as the previous one so i can select a department and it show the different types and amount of each if possible

 

[davidmor]

ive managed to do it (somehow), the formula i used probably isnt the correct way of doing it but it does the job and displays the requested info, thanks for giving me the begining info that allowed me to try and create what i was after


never thought id get so excited about figuring somehting out in excel lol

 

[Taul]

Hi,
Great news, sounds like you have caught the Excel bug.
I had a part solution done, so I thought I would post it anyway.
The pie chart is intended to read from Column "U" to display each month as it is selected.
This is based on having all the years worth of data on one page (col A to D), no real need for separate page tabs for each month.
Formula goes down to row 1000, change it if needed.

Filter Pie Chart.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Date	Location	Info	Category
2	01/07/2020	Cutting	stuff here	Environmental		Location
3	02/07/2020	Finishing Cell	stuff here	Slip/Trip/Fall		All
4	02/07/2020	Flatwork	stuff here	Fire Hazard
5	06/07/2020	Cutting	stuff here	Chemical Control		Month # =	1	2	3	4	5	6	7	8	9	10	11	12			7
6	02/07/2020	Finishing Cell	stuff here	Machinary/Equipment		Category	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total		Jul
7	02/07/2020	Cutting	stuff here	Chemical Control		Chemical Control	0	0	0	0	0	0	3	0	0	0	0	0	3		3
8	13/07/2020	Cutting	stuff here	Housekeeping		Environmental	0	0	0	0	0	0	5	0	0	0	0	0	5		5
9	02/07/2020	Finishing Cell	stuff here	Environmental		Fire Hazard	0	0	0	0	0	0	4	0	0	0	0	0	4		4
10	02/07/2020	Flatwork	stuff here	Slip/Trip/Fall		Housekeeping	0	0	0	0	0	0	4	0	0	0	0	0	4		4
11	06/07/2020	Cutting	stuff here	Chemical Control		Machinary/Equipment	0	0	0	0	0	0	4	0	0	0	0	0	4		4
12	02/07/2020	Finishing Cell	stuff here	Personnel/PPE		Personnel/PPE	0	0	0	0	0	0	4	0	0	0	0	0	4		4
13	02/07/2020	Flatwork	stuff here	Machinary/Equipment		Process Proceedure	0	0	0	0	0	0	3	0	0	0	0	0	3		3
14	13/07/2020	Cutting	stuff here	Process Proceedure		Slip/Trip/Fall	0	0	0	0	0	0	4	0	0	0	0	0	4		4
15	02/07/2020	Finishing Cell	stuff here	Housekeeping		Vehicles/Transport	0	0	0	0	0	0	1	0	0	0	0	0	1		1
16	02/07/2020	Flatwork	stuff here	Environmental		Total =	0	0	0	0	0	0	32	0	0	0	0	0	32		32
17	06/07/2020	Cutting	stuff here	Vehicles/Transport
18	02/07/2020	Finishing Cell	stuff here	Fire Hazard
19	02/07/2020	Flatwork	stuff here	Personnel/PPE
20	13/07/2020	Cutting	stuff here	Machinary/Equipment
21	02/07/2020	Finishing Cell	stuff here	Process Proceedure
22	02/07/2020	Flatwork	stuff here	Housekeeping
23	06/07/2020	Cutting	stuff here	Environmental
24	02/07/2020	Finishing Cell	stuff here	Slip/Trip/Fall
25	02/07/2020	Flatwork	stuff here	Fire Hazard
26	13/07/2020	Cutting	stuff here	Personnel/PPE
27	02/07/2020	Finishing Cell	stuff here	Machinary/Equipment
28	02/07/2020	Flatwork	stuff here	Process Proceedure
29	13/07/2020	Warehouse	stuff here	Housekeeping
30	02/07/2020	offices	stuff here	Environmental
31	02/07/2020	Gluing	stuff here	Slip/Trip/Fall
32	13/07/2020	Flatwork	stuff here	Fire Hazard
33	02/07/2020	Rework	stuff here	Personnel/PPE
Sheet1

Cell Formulas
Range	Formula
U5	U5	=MONTH(1&LEFT(U6,3))
G7:R15	G7	=IF($F$3="All",SUMPRODUCT(--($D$2:$D$1000=$F7),--(MONTH($A$2:$A$1000)=G$5)),SUMPRODUCT(--($B$2:$B$1000=$F$3),--($D$2:$D$1000=$F7),--(MONTH($A$2:$A$1000)=G$5)))
S7:S15	S7	=SUM(G7:R7)
U16,G16:S16	G16	=SUM(G7:G15)
U7:U15	U7	=INDEX($G7:$R7,MATCH($U$5,$G$5:$R$5))