# Help needed with Countifs formula

#### kerry9439

##### New Member
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
ABCD
1Bmi 30 >
2White British>302
3Asian>300
4Polish>300
5African>301
6American>300
7
8BMI 25-29.9
9White British>25<29.92
10Asian>25<29.9#VALUE!
11Polish>25<29.9#VALUE!
12African>25<29.9#VALUE!
13American>25<29.9#VALUE!
14
15BMI 18-24.9
16White British>18 <24.9
17Asian>18 <24.9
18Polish>18 <24.9
19African>18 <24.9
20American>18 <24.9
21
GDM Stats
Cell Formulas
RangeFormula
D2:D6D2=COUNTIFS('GDM Database'!\$F\$3:\$F\$10,'GDM Stats'!A2,'GDM Database'!T3:T10,B2)
D9:D13D9=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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
302/01/2020Annabel Smith28/10/2000D159824African14/04/2020405.824+2YesNo32.207/04/202039+0SVD3.995kgMaleYesNo
402/01/2020Snow White19/05/1992D123456white british25/08/2020Previous GDM wishes to assumeN/ANo28.4########04/08/202037+0LSCS3.552kgFemaleNoYes
504/01/2020Janice Baker30/06/1996D147852white british25/04/2020446.524+5435.336+2YesYes35.4########06/04/202037+2forceps4.511kgFemaleYesNo
604/01/2020Julie smith16/10/1989D123456white british15/04/2020395.224+1NoNo30.5
704/01/2020Jasmine Price25/09/1999D654321asian16/04/2020486.724+4ALT 18 / AST 15YesNo25.2
806/01/2020Kerry Conolly14/04/1986D789321white british23/07/2020Previous GDM wishes to assumeN/ANo29.8########
906/01/2020Jo Wise06/08/1993D767652polish20/05/2020335.213+2NoNo33.4
1008/01/2020Becky Dainty28/07/1987D412354American05/06/2020305.712+0NoNo26.6
11
12
GDM Database
Cells with Conditional Formatting
CellConditionCell FormatStop If True
T3:T15Cell Valuebetween 30 and 60textNO
T3:T15Cell Valuebetween 26 and 29.9textNO
T3:T15Cell Valuebetween 18 and 25.9textNO

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### 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

##### New Member
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

Replies
33
Views
690
Replies
6
Views
105
Replies
10
Views
381
Replies
4
Views
89
Replies
8
Views
104

1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back