need to combine two formulas

[AIQ]

Hello All,
I need help combining two countif formulas. I have 2 columns (age and diagnosis). I need to combine these two formulas:
=COUNTIF(I:I,"*Granuloma*")
=COUNTIF(E:E,"<20")
Goal is to count how many patients <20 has the diagnosis "Granuloma". Please make sure your formula take into account the asterisk because diagnosis column can have "Granuloma" plus another diagnosis in the same cell such as "Granuloma, scar".
Please help me .... I've been trying to find a correct formula for the past 2 days without any luck

 

[jeffreybrown]

Try...

=SUMPRODUCT(--(E:E<20),--ISNUMBER(SEARCH("Granuloma",I:I)))

It's a good idea to try and limit your range instead of the entire column.

If you have 2007...try...

=COUNTIFS(E:E,"<20",I:I,"*"&"Granuloma"&"*")

 

[AIQ]

Thanks Jeffery,
Seems it work but can you ask you for something else. I have another age group which is between 20-50 year old.
This is the countif formula for the age group: =COUNTIF(E:E,">=20")-COUNTIF(E:E,">50")

How can I substitute this age group in your formula?

 

[jeffreybrown]

2003
=SUMPRODUCT(--(E1:E10>=20),--(E1:E10<=50),--ISNUMBER(SEARCH("Granuloma",I1:I10)))

2007
=COUNTIFS(E1:E10,">=20",E1:E10,"<=50",I1:I10,"*"&"Granuloma"&"*")

Adjust your ranges to fit

 

[AIQ]

Thanks a lot Jeffery ... everything looks great

 

[jeffreybrown]

You're welcome and glad it worked for you

 

[MR3]

hey Jeffrey,

how would you limit the range of the column data?
like what if his column data varied in length how would you be able to dectect the end & what if one row had a blank in it?

 

[AIQ]

Jeffrey I have another favor please.
I need a formula that count the number of cells that contains certain numbers. For example, Column D has values from (1-32) ... I need to count number of cells that contain number from (1-3), (4-6), (23-27), ect. Is there a formula that can do that?
Column D
1
3
6
17
8
25
30
9
Need to count number of cells that contain numbers (3-6). ANSWER=2

 

[Aladin Akyurek]

Jeffrey I have another favor please.
I need a formula that count the number of cells that contains certain numbers. For example, Column D has values from (1-32) ... I need to count number of cells that contain number from (1-3), (4-6), (23-27), ect. Is there a formula that can do that?
Column D
1
3
6
17
8
25
30
9
Need to count number of cells that contain numbers (3-6). ANSWER=2

Let J2 house 3 and K2 6...

In L2 try...

=COUNTIF($D$2:$D$9,">="&J2)-COUNTIF($D$2:$D$9,">"&K2)

Note that the count is inclusive, that is, it also includes both the lower and upper bound values of 3 and 6.

 

[Aladin Akyurek]

hey Jeffrey,

how would you limit the range of the column data?
like what if his column data varied in length how would you be able to dectect the end & what if one row had a blank in it?

Whic formula are you addressing exactly and which range the formula refers to has a blank?