Getpivotdata formula error retuning #ref

[LucyWalker]

Hi

I work for a preschool in the UK and have developed my own spreadsheets and learnt how to insert pivot tables to run a number of reports.

I am stumped though.

I run a daily breakdown of children and staff so that we can ensure that we are staffed in the correct ratio of adults to children for each age group.

In the attached spreadsheet you will see I have Mon am session and Tues am session - in the boxes formatted orange the figures work absolutely perfectly for Mon AM but for Tue AM i keep getting the #REF error and I can't for the life of me work out why.

I have tried hunting for an if or formula wondering if when the figure is only 1 it for some reason doesn't or can't count it?

I am not an it guru nor a maths nerd just someone who has googled and come up with a system that works so any help in a very simply explained way would be really appreciated.

Thank you in advance
Lucy

Query formula work book get pivottable formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Boys/Girls	Staff not on register	First Name	Last Name	Ethinicity	EAL	AL additional language	SEN	2yr funding	Expected EYPP for child moving from 2yr funding to 3/4yr funding -economic grounds	Disadvant/ S&L	Session	DOB	Age	Start Date & additional sessions	End date of sessions	Funding Date	Eligible 3yrs funding	Shared Setting	30 hrs funding	Hours claimed at Bright Sparks Universal	Hours claimed at Bright Sparks Extended	Hours claimed at Shared setting	Key person	Hours Per Session	13.00	Staff Hours	Health Notes	Avg temp
2	girls		Sarah	Smith	WBRI							Mon AM	12/12/2019	2	12/05/2021		31/03/2022	2.2										Medical: Allergy to hazlenuts	36.4c
3	boys		Stan	Smith	WBRI							Mon AM	01/02/2018	4	12/05/2021		31/03/2022	4.1										Medical: Allergy to hazlenuts	36.4c
4	girls		Elizabeth	*******	WBRI							Mon AM	25/06/2020	1	12/05/2021		31/03/2022	1.7										Medical: Allergy to hazlenuts	36.4c
5	girls		Elizabeth	*******	WBRI							Tue AM	25/06/2020	1	12/05/2021		31/03/2022	1.7										Medical: Allergy to hazlenuts	36.4c
6	girls		Sarah	Smith	WBRI							Tue AM	12/12/2014	7	12/05/2021		31/03/2022	7.2										Medical: Allergy to hazlenuts	36.4c
7	Staff		Louise	Jones	WBRI							Mon AM	12/05/1978	43	12/05/2021		31/03/2022	43.8										Medical: Allergy to hazlenuts	36.4c
8	Staff		Louise	Jones	WBRI							Tue AM	12/05/1978	43	12/05/2021		31/03/2022	43.8										Medical: Allergy to hazlenuts	36.4c
9
10
11
12
13	End date of sessions	(All)
14	Session	Mon AM
15	Start Date & additional sessions	(All)
16
17	Boys/Girls	First Name	Last Name	DOB	Age	Ethinicity	Health Notes	Count of Boys/Girls	Count of Health Notes	Count of EAL	Count of AL additional language	Count of SEN	Count of 2yr funding	Count of Expected EYPP for child moving from 2yr funding to 3/4yr funding -economic grounds	Count of Disadvant/ S&L	Count of 30 hrs funding
18	boys	Stan	Smith	01/02/2018	4	WBRI	Medical: Allergy to hazlenuts	1	1
19	boys Total							1	1
20	girls	Sarah	Smith	12/12/2019	2	WBRI	Medical: Allergy to hazlenuts	1	1
21	girls	Elizabeth	*******	25/06/2020	1	WBRI	Medical: Allergy to hazlenuts	1	1
22	girls Total							2	2
23	Staff	Louise	Jones	12/05/1978	43	WBRI	Medical: Allergy to hazlenuts	1	1
24	Staff Total							1	1
25
26
27
28	Mon AM
29	TOTAL NUMBER OF CHILDREN EXPECTED	3
30	TOTAL No of 18 month OLDS	1
31	TOTAL No of 2yr OLDS	1
32	Total No of 3/4yr OLDs	1
33
34
35
36
37
Sheet1

Cell Formulas
Range	Formula
N2:N8	N2	=INT((TODAY()-M2)/365.25)
R2:R8	R2	=ROUNDDOWN((Q2-M2)/365.25,1)
A28	A28	=B14
B29	B29	=GETPIVOTDATA("Count of Boys/Girls",$A$17,"Boys/Girls","boys")+GETPIVOTDATA("Count of Boys/Girls",$A$17,"Boys/Girls","girls")
B30	B30	=COUNTIF($E:$E,1)
B31	B31	=COUNTIF($E:$E,2)
B32	B32	=COUNTIF($E:$E,3)+COUNTIF($E:$E,4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L1	Cell	contains a blank value	text	NO

 

[RoryA]

Welcome to the forum.

It doesn't look like you have any boys for Tues AM, so that first GETPIVOTDATA function will return an error. You can handle that with IFERROR:

=IFERROR(GETPIVOTDATA("Count of Boys/Girls",$A$17,"Boys/Girls","boys"),0)+IFERROR(GETPIVOTDATA("Count of Boys/Girls",$A$17,"Boys/Girls","girls"),0)

 

[LucyWalker]

Welcome to the forum.

It doesn't look like you have any boys for Tues AM, so that first GETPIVOTDATA function will return an error. You can handle that with IFERROR:

=IFERROR(GETPIVOTDATA("Count of Boys/Girls",$A$17,"Boys/Girls","boys"),0)+IFERROR(GETPIVOTDATA("Count of Boys/Girls",$A$17,"Boys/Girls","girls"),0)

Hi Rory,

That is exactly right it only happended when I had one boy or one girl and i just couldn't work out where to put the iferror, or if it should be a greater than and a number of other random thingsetc etc Thank you so much for your speedy reply and it certainly works!!

Lucy (one happy Pivot table lady

 

[RoryA]

Glad we could help.