drag down the text in criteria along with cell range while using countifs

%3CLINGO-SUB%20id%3D%22lingo-sub-3065355%22%20slang%3D%22en-US%22%3Edrag%20down%20the%20text%20in%20criteria%20along%20with%20cell%20range%20while%20using%20countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3065355%22%20slang%3D%22en-US%22%3E%3CTABLE%20width%3D%22319px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22155.417px%22%3ERisk%20of%20Injury%3C%2FTD%3E%3CTD%20width%3D%2257.8438px%22%3ENov-21%3C%2FTD%3E%3CTD%20width%3D%2247.9479px%22%3EDec-21%3C%2FTD%3E%3CTD%20width%3D%2256.7917px%22%3Eproblem%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22155.417px%22%3ESlip%20and%20Trip%3C%2FTD%3E%3CTD%20width%3D%2257.8438px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2247.9479px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2256.7917px%22%3E%3DCOUNTIF(SO!%24E%24769%3A%24E%24793%2C%22Slip%20and%20Trip%22)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22155.417px%22%3ECut%20injury%3C%2FTD%3E%3CTD%20width%3D%2257.8438px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2247.9479px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2256.7917px%22%3E%3DCOUNTIF(SO!%24E%24769%3A%24E%24793%2C%22Cut%20injury%22)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22155.417px%22%3EHousekeeping%3C%2FTD%3E%3CTD%20width%3D%2257.8438px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2247.9479px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2256.7917px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22155.417px%22%3EPhysical%20injury%3C%2FTD%3E%3CTD%20width%3D%2257.8438px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2247.9479px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2256.7917px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EWhile%20dragging%20down%20the%20formula%2C%20text%20slip%20and%20trip%20or%20cut%20injury%20or%20housekeeping%20not%20changing%20according%20to%20the%20risk%20of%20injury%20column.%3C%2FP%3E%3CP%3EIt%20only%20copying%20the%20same%20formula.%26nbsp%3B%3DCOUNTIF(SO!%24E%24769%3A%24E%24793%2C%22Slip%20and%20Trip%22)%20in%20all%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eneed%20solution%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3065355%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor
Risk of InjuryNov-21Dec-21problem
Slip and Trip20=COUNTIF(SO!$E$769:$E$793,"Slip and Trip")
Cut injury36=COUNTIF(SO!$E$769:$E$793,"Cut injury")
Housekeeping00 
Physical injury10 

While dragging down the formula, text slip and trip or cut injury or housekeeping not changing according to the risk of injury column.

It only copying the same formula. =COUNTIF(SO!$E$769:$E$793,"Slip and Trip") in all cells.

 

need solution

 

2 Replies

@Mukesh8734 Rather than including the part "Slip and Trip", use a reference to the cell (without $ signs) that contains that phrase. The formula could then look like:

 

=COUNTIF(SO!$E$769:$E$793,A2)

 

Copy it down and A2 will change to A3 and so on.

Thanks a lot sir, you make my work very easy ad it saves 2 hours for me.
Thank you once again