countif

[shadedlight]

Hello,

Here's my problem. In column A and B are used as a reference for a countif formula in column E:I

The problem I'm running into is that I want part of the formula to change and keep some of the formula the same as I fill it down a column. Here's an example.

All of column E is using a countif formula with sweating is what I want to look up but where the formula looks is going to change. So, D3, the where the formula looks is going to be different than where it looks in D4 but 'Sweating' will be the same. I then want to be able to fill the column down. The rest of the columns would follow same logic.

The reason why, is because this is going to be a growing list that will become rather large.

Thank you.

 

[David Evans]

In a cursory look at your worksheet, there are a few warning signs - you have merged cells which are best avoided. Also, I thing D1:I1 should actually contain the contents that are currently showing in D2:I2. Also Column C is empty - is that by design? D3 & D4 you referred to contain no formulas, merely text

Tell us what you expect the answers to be in the cells you want calculated.

 

[shadedlight]

Unmerging the cells would be okay. Column C is by design.

I was imagining that column D would reference column A:B columns.

See the newly uploaded file

Column E3:I3 are the correct answers but when I fill the column down it is not adjusting with correct values.


Guizhi Jia Huopo Xingren Tang's value for Sweating (E5) should not have a value of 1, it should be zero.

Guizhi Jia Gegen Tang's value for Heat Effusion (F4) Should not have a value of 1, it should be zero.

Sorry for the confusion

 

[David Evans]

Why do you have two columns with the title "Formula"?

Give us the answer you expect to find in a range of cells, for example E2:I4. Then explain the calculation for a few of them also.

Explain why, for example, "Guizhi Jia Gegen Tang's value for Heat Effusion (F4) Should not have a value of 1, it should be zero." What cells is F4 dependent on?

 

[shadedlight]

Here's what I'm trying to accomplish. I need a count of occurrences of word in column B. For example, In column B Sweating has 2 occurrences, Aversion to wind has 2 occurrences, Stretched stiff nape and neck has 1 occurrence, etc.

Is this easier way to do this?

The reason I have two columns with the title "Formula" is because the countif formula's in the E:I columns are based on the Signs and symptoms in column B.

I've taken the Signs and Symptoms column (B) and placed each of them starting in column E, F, G, and so on.

"Guizhi Jia Gegen Tang" should not have a value of 1 because I want the countif formula for "Guizhi Jia Gegen Tang" D3 for Sweating to reference B3

F4 should be dependent on =COUNTIF(B8,"Heat effusion")

 

[rahulshewale1]

Hii @shadedlight ,

see if is it ok ?

Cell E2 Copy across,
=IFERROR(IF(INDEX(OFFSET($A$1,MATCH($D2,$A$1:$A$100,0)-1,1,4,1),MATCH(E$1,OFFSET($A$1,MATCH($D2,$A$1:$A$100,0)-1,1,4,1),0))>0,1,0),0)

Regard
Rahul shewale

 

[bosco_yip]

Try,

1] D2, formula copied down :

=IFERROR(INDEX(A$2:A$8,AGGREGATE(15,6,ROW(A$2:A$8)-ROW(A$1)/(A$2:A$8<>""),ROWS($1:1))),"")

2] In E1, formula copied across right :

=IFERROR(INDEX($B$2:$B$8,MATCH(0,INDEX(COUNTIF($D$1:D1,$B$2:$B$8&""),0),0)),"")

3] In E2, formula copied across right and all copied down :

=SUMPRODUCT((LOOKUP(ROW($A$2:$A$8),ROW($A$2:$A$8)/($A$2:$A$8>0),$A$2:$A$8)=$D2)*($B$2:$B$8=E$1))

Regards
Bosco

 

[shrivallabha]

See if attached file helps your case if you choose to fill cells (yellow marked).
Formula in E2:
=NOT(ISNA(LOOKUP(2,1/($D2&E$1=$A$1:$A$8&$B$1:$B$8))))+0
Copy down and across.