Help needed with Countifs formula

kerry9439

New Member
Joined
Nov 14, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

kerry9439

New Member
Joined
Nov 14, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
Thank you so much for your help, this worked. :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,404
Messages
5,636,088
Members
416,897
Latest member
YAFI

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top