Help needed with Countifs formula

[kerry9439]

Hi,

I am trying to work out some statistics using formulas. The statistics are going to be on a different tab but in the same workbook. I am trying to work out how many of each ethnic Group have a BMI between a certain amount. Countifs is the formula that i have chosen to use is this the correct formula to use to answer this?
I have done the first two and this works fine, but when i try and copy this by dragging the box down to copy the formula it comes back with #VALUE. I've tried looking a online tutorials but nothing is helping.

Kind Regards
Kerry

GDM Database Example.xlsx
	A	B	C	D
1	Bmi 30 >
2	White British	>30		2
3	Asian	>30		0
4	Polish	>30		0
5	African	>30		1
6	American	>30		0
7
8	BMI 25-29.9
9	White British	>25	<29.9	2
10	Asian	>25	<29.9	#VALUE!
11	Polish	>25	<29.9	#VALUE!
12	African	>25	<29.9	#VALUE!
13	American	>25	<29.9	#VALUE!
14
15	BMI 18-24.9
16	White British	>18 <24.9
17	Asian	>18 <24.9
18	Polish	>18 <24.9
19	African	>18 <24.9
20	American	>18 <24.9
21
GDM Stats

Cell Formulas
Range	Formula
D2:D6	D2	=COUNTIFS('GDM Database'!$F$3:$F$10,'GDM Stats'!A2,'GDM Database'!T3:T10,B2)
D9:D13	D9	=COUNTIFS('GDM Database'!F3:$F$10,'GDM Stats'!A9,'GDM Database'!T3:T10,'GDM Stats'!B9,'GDM Database'!F3:$F$10,'GDM Stats'!A9,'GDM Database'!T3:T10,'GDM Stats'!C9)

GDM Database Example.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
3		02/01/2020	Annabel Smith	28/10/2000	D159824	African	14/04/2020	40	5.8	24+2								Yes	No	32.2			07/04/2020	39+0	SVD	3.995kg	Male	Yes	No
4		02/01/2020	Snow White	19/05/1992	D123456	white british	25/08/2020	Previous GDM wishes to assume										N/A	No	28.4		########	04/08/2020	37+0	LSCS	3.552kg	Female	No	Yes
5		04/01/2020	Janice Baker	30/06/1996	D147852	white british	25/04/2020	44	6.5	24+5	43	5.3	36+2					Yes	Yes	35.4	########		06/04/2020	37+2	forceps	4.511kg	Female	Yes	No
6		04/01/2020	Julie smith	16/10/1989	D123456	white british	15/04/2020	39	5.2	24+1								No	No	30.5
7		04/01/2020	Jasmine Price	25/09/1999	D654321	asian	16/04/2020	48	6.7	24+4							ALT 18 / AST 15	Yes	No	25.2
8		06/01/2020	Kerry Conolly	14/04/1986	D789321	white british	23/07/2020	Previous GDM wishes to assume										N/A	No	29.8		########
9		06/01/2020	Jo Wise	06/08/1993	D767652	polish	20/05/2020	33	5.2	13+2								No	No	33.4
10		08/01/2020	Becky Dainty	28/07/1987	D412354	American	05/06/2020	30	5.7	12+0								No	No	26.6
11
12
GDM Database

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T3:T15	Cell Value	between 30 and 60	text	NO
T3:T15	Cell Value	between 26 and 29.9	text	NO
T3:T15	Cell Value	between 18 and 25.9	text	NO

 

[Joe4]

It seems that you only locked down your first range, but no your other ones, i.e.:
LOCKED CORRECTLY: 'GDM Database'!$F$3:$F$10
NOT LOCKED: 'GDM Stats'!A2,'GDM Database'!T3:T10

If you do not lock the range, as you copy the formula down, the row numbers are going to shift too.
So add the "$" in front of the row reference in the other ranges too, i.e.
'GDM Stats'!A2,'GDM Database'!T$3:T$10

 

[kerry9439]

Thank you so much for your help, this worked.

 

[Joe4]

You are welcome.

I hope it makes sense why you need to do that. If not, feel free to ask any questions.
Here is a nice little write-up that discusses it: Excel Mixed reference | Exceljet