HELP ME PLEASE
It has been many many years since I worked with excel - so I am definitely NOT an expert (once upon a time) and am desperately hoping that someone can help me.
My boss has asked me to set up a spreadsheet and so far so good all my formulas have worked until I have become brain dead.
I have two worksheets - 1 is enter data the other is working data - in enter I have two columns - 1 is col D with M (male) & F (female) in col E I have their ages - in my working data I have two columns to auto caluclate the number of males and the number of females between age brackets of 5 years starting at 16-20, 25-30, 31-35 and so forth.
The formula I am putting in is not working and I know it is wrong but for the life of me I cannot work out the correct one - this is what I have so far - =COUNTIF('GSTONE DATA'!D:D,"F""E:E<=30") - PLEASE HELP ME IF YOU CAN.
Maureen
- Ladyseahawk's blog
- Login or register to post comments
- 4678 reads
Countifs and SumProduct
You can use either COUNTIFS function of SUMPRODCUT function to arrive at the solution
I wish I can send excel file as attachment (no email address of yours or not sure how to attach here), I will explain as much as I can
Formula in E3 is =COUNTIFS($A$3:$A$31,E$2,$B$3:$B$31,">="&$C3,$B$3:$B$31,"<="&$D3)
Formula in F3 is =COUNTIFS($A$3:$A$31,F$2,$B$3:$B$31,">="&$C3,$B$3:$B$31,"<="&$D3)
Formula in G3 is =SUMPRODUCT(--($A$3:$A$31=G$2)*--($B$3:$B$31>=$C3)*--($B$3:$B$31<=$D3))
Formula is H3 is =SUMPRODUCT(--($A$3:$A$31=H$2)*--($B$3:$B$31>=$C3)*--($B$3:$B$31<=$D3))
-------COLUMNS-------------------------------------------------
A B C D E F G H
COUNIFS SUMPRODUCT
Sex Age From Age M F M F
M 25 16 20 1 2 1 2
F 57 21 25 2 1 2 1
M 17 26 30 0 0 0 0
F 18 31 35 0 1 0 1
M 25 36 40 1 0 1 0
F 33 41 45 0 0 0 0
F 49 46 50 2 1 2 1
M 49 51 55 0 0 0 0
F 23 56 60 0 1 0 1
M 48
F 19
M 37
Reply to Comment
you did not mention which version of Excel you are on. if its 2007 and above then COUNTSIFS function can be used. However, in 2003 and below versions you can not have multiple criterias for countif and hence can not be used in this situation. However, you can use Array Formulas to get the result you want get. Do a bit of googling for Array Formulas and you will find the answer.