Formula that moves down a row when you move right a column

%3CLINGO-SUB%20id%3D%22lingo-sub-2267964%22%20slang%3D%22en-US%22%3EFormula%20that%20moves%20down%20a%20row%20when%20you%20move%20right%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2267964%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20columns%20C-G%20I%20have%20certain%20letters%2C%20and%20I%20want%20to%20see%20whether%20any%20letters%20in%20row%203%20match%20those%20in%20rows%203-100%2C%20any%20in%20row%204%20match%20those%20in%203-100%20etc.%20I've%20sent%20up%20the%20following%20formula%20(this%20is%20from%20J4%2C%20which%20will%20see%20if%20any%20in%20row%203%20match%20row%205%2C%20and%20I%20can%20drag%20this%20down%20column%20H%20so%20that%20it%20always%20compares%20the%20row%20with%20row%205)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(%24C3%3A%24G3%2C%24C%245)%2BCOUNTIF(%24C3%3A%24G3%2C%24D%245)%2BCOUNTIF(%24C3%3A%24G3%2C%24E%245)%2BCOUNTIF(%24C3%3A%24G3%2C%24F%245)%2BCOUNTIF(%24C3%3A%24G3%2C%24G%245)%3C%2FP%3E%3CP%3E(There's%20probably%20a%20simpler%20way%20to%20do%20this!)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20then%20want%20to%20drag%20the%20equation%20over%20to%20column%20K%2C%20and%20so%20on%2C%20and%20make%20it%20always%20look%20at%20the%20next%20row%20down%2C%20so%20K%20always%20checks%20against%206C-G%2C%20L%20always%20against%207C-G%20and%20so%20on.%20I%20don't%20know%20how%20to%20make%20it%20so%20that%20it%20works%20when%20I%20drag%20it%20down%2C%20but%20also%20works%20when%20I%20drag%20it%20across.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20possible%2C%20I'd%20also%20love%20to%20see%20if%20sequences%20are%20the%20same%20in%20other%20rows.%20For%20example%2C%20if%20one%20row%20contains%20A%20B%20C%20D%20E%2C%20I'd%20like%20to%20see%20if%20others%20contain%20A%20B%20in%20the%20same%20position%2C%20or%20ABC%2C%20BCD%2C%20CDE%2C%20DE%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense!%20Thank%20you%20for%20even%20getting%20this%20far!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2267964%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

In columns C-G I have certain letters, and I want to see whether any letters in row 3 match those in rows 3-100, any in row 4 match those in 3-100 etc. I've sent up the following formula (this is from J4, which will see if any in row 3 match row 5, and I can drag this down column H so that it always compares the row with row 5):

 

=COUNTIF($C3:$G3,$C$5)+COUNTIF($C3:$G3,$D$5)+COUNTIF($C3:$G3,$E$5)+COUNTIF($C3:$G3,$F$5)+COUNTIF($C3:$G3,$G$5)

(There's probably a simpler way to do this!)

 

However, I then want to drag the equation over to column K, and so on, and make it always look at the next row down, so K always checks against 6C-G, L always against 7C-G and so on. I don't know how to make it so that it works when I drag it down, but also works when I drag it across.

 

If possible, I'd also love to see if sequences are the same in other rows. For example, if one row contains A B C D E, I'd like to see if others contain A B in the same position, or ABC, BCD, CDE, DE etc.

 

I hope that makes sense! Thank you for even getting this far! 

1 Reply

@Jodes1675 

In J4:

=SUMPRODUCT(COUNTIF($C3:$G3,INDEX($C$5:$G$100,COLUMN(J4)-COLUMN($J$4)+1,0)))

 

Change 100 if your range extends below row 100.

You can fill this to the right and down.