need help with formula

AIQ

New Member
Joined
Aug 6, 2011
Messages
15
I need to find a correct formula that solve this issue. I have 2 columns (Age and Diagnosis). I need to count the number of times a "specific" diagnosis (X) appear among certain age group (<20, 20-50, >50). I think it should be a sumproduct formula but I am not sure how to formulate it.
Here is an example of he spread sheet
Age Diagnosis
10 X
14 X
34 Y
22 Y
55 Z
29 Z
60 X


Thanks in advance for your help
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Thanks Yahya,
I think you link explains how to count number of people in certain age bracket. However, I need a formula that count both conditions (age) and (Diagnosis). For example, I need to know how many people <20 has the diagnosis (X). OR, how many people between the age 20-50 has diagnosis (Y). Result has to fulfill both conditions (correct age group and correct diagnosis)
Thanks
Abdullah
 
Upvote 0
Well, you don't necessarily need a formula for that. You can just add a column that contains all 1 - in you case:

Age Diagnosis Index
10 X 1
14 X 1
...

and in the end make a pivot table where Age is the row, Diagnosis is the column and Index is the data (in Pivot Table => Pivot Table Wizard => Layout Designer)
 
Upvote 0
Zuslan,
I used a formula to count diagnosis and gender. So, I figured another formula to count number of cells that specific diagnosis appear among certain age group. In addition, I am not an excel expert and I dont know how to use pivot table. Is there a simple formula to use?
 
Upvote 0
is this what you want
Excel Workbook
ABCDEFG
1AgeDiagnosis<2020-50>50
210XX200
314XY020
434YZ011
522Y
655Z
729Z
860X
Sheet5
Excel 2007
Cell Formulas
RangeFormula
E2=SUMPRODUCT(--($A$2:$A$8<20),--($B$2:$B$8=$D2))
F2=SUMPRODUCT(--($A$2:$A$8>=20),--($A$2:$A$8<=50),--($B$2:$B$8=$D2))
G2=SUMPRODUCT(--($A$2:$A$8>50),--($B$2:$B$8=$D2))
 
Upvote 0
Thanks Yahya,
Formula seems to work. However, sometime cells have 2 diagnosis for 1 patient such as (X, Y). So I tried
=SUMPRODUCT(--($E$2:$E$2500>50),--($I$2:$I$2500="*X*"))
But it seems asterisk cant be used with this formula. Is there a way to fix the formula so it count cell that would have X alone or in combination with another diagnosis?
Thanks again
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

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
Back
Top