HOW TO: Use ADDRESS within COUNTIFS

%3CLINGO-SUB%20id%3D%22lingo-sub-263829%22%20slang%3D%22en-US%22%3EHOW%20TO%3A%20Use%20ADDRESS%20within%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-263829%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20file%20like%20the%20one%20attached.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20trying%20to%20get%20the%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(COUNTIFS(A4%3AF4%2C%22*%22%26amp%3BA2%26amp%3B%22*%22%2CA4%3AF4%2C%22*%22%26amp%3BB2%26amp%3B%22*%22)%26gt%3B%3D1%2C%22%3AD%22%2C%22%3Ac%22)%3CBR%20%2F%3ETo%20return%20the%20cell%20address%20where%20the%20statement%20is%20true%20(instead%20of%20the%20silly%20%22%3AD%22).%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EFor%20instance%20in%20that%20file%20I%20would%20want%20it%20to%20return%20the%20value%20%22A4%22%20since%20that%20is%20the%20cell%20where%20the%202%20conditions%20of%20the%20countifs%20were%20true.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20this%20be%20done%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%3C%2FP%3E%3CUL%3E%3CLI%3EBasically%20what%20I%20am%20trying%20to%20do%20is%20to%20have%202%20formulas%20incorporated%20in%20one.%3CBR%20%2F%3EThe%20first%20one%20will%20find%20specific%20text%20in%20a%20range%20so%20that%20it%20can%20do%20X%20if%20true%20and%20Y%20if%20false.%3CBR%20%2F%3EFor%20this%20I%20originally%20tried%20COUNTIFS%2C%20LOOKUP%20and%20HLOOKUP%2C%20which%20do%20work%20more%20or%20less%20how%20I%20want%2C%20with%20HLOOKUP%20being%20the%20closest.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EThe%20second%20one%20(X)%20would%20then%20do%20a%20MID%20on%20the%20cell%20where%20the%20first%20formula%20was%20true%2C%20so%20that%20I%20can%20obtain%20the%20info%20I%20want.%3CBR%20%2F%3EThe%20problem%20here%20is%20that%20for%20MID%20I%20have%20to%20specify%20the%20cell%20where%20I%20want%20to%20use%20the%20mid%20on%2C%20which%20I%20cant%20because%20I%20need%20it%20to%20be%20dynamic%20(aka%3A%20the%20cell...%20any%20cell%2C%20where%20the%20condition%20was%20true)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI've%20included%20another%20excel%20file%20that%20better%20reflects%20this%20edit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-263829%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264644%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20Use%20ADDRESS%20within%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264644%22%20slang%3D%22en-US%22%3E%3CP%3ETwo%20files%20with%20different%20setups.%3C%2FP%3E%3CP%3EIt%20is%20still%20not%20clearer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264162%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20Use%20ADDRESS%20within%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264162%22%20slang%3D%22en-US%22%3Eoh%2C%20I%20am%20sorry%2C%20I%20am%20editing%20the%20original%20post%20to%20reflect%20what%20I%20am%20actually%20trying%20%3A)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-263844%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20Use%20ADDRESS%20within%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-263844%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20think%20you%20want%20to%20get%20A4.%3C%2FP%3E%3CP%3EI%20think%20you%20have%20a%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fmeta.stackexchange.com%2Fquestions%2F66377%2Fwhat-is-the-xy-problem%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EXY%20problem%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a file like the one attached.

I am trying to get the formula:

=IF(COUNTIFS(A4:F4,"*"&A2&"*",A4:F4,"*"&B2&"*")>=1,":D",":c")
To return the cell address where the statement is true (instead of the silly ":D").

For instance in that file I would want it to return the value "A4" since that is the cell where the 2 conditions of the countifs were true.

How can this be done?

 

Edit:

  • Basically what I am trying to do is to have 2 formulas incorporated in one.
    The first one will find specific text in a range so that it can do X if true and Y if false.
    For this I originally tried COUNTIFS, LOOKUP and HLOOKUP, which do work more or less how I want, with HLOOKUP being the closest.

 

  • The second one (X) would then do a MID on the cell where the first formula was true, so that I can obtain the info I want.
    The problem here is that for MID I have to specify the cell where I want to use the mid on, which I cant because I need it to be dynamic (aka: the cell... any cell, where the condition was true)


I've included another excel file that better reflects this edit.

 

 

3 Replies

 

I don't think you want to get A4.

I think you have a XY problem.

oh, I am sorry, I am editing the original post to reflect what I am actually trying :)

Two files with different setups.

It is still not clearer.