SOLVED

Dynamic Lookup for COUNTIF

%3CLINGO-SUB%20id%3D%22lingo-sub-2353542%22%20slang%3D%22en-US%22%3EDynamic%20Lookup%20for%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2353542%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWondering%20if%20this%20is%20possible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20items%20in%20Column%20J%20and%20L%20are%20a%20list%20lookup%20(auto%20sorted%20alphabetically)%20from%20another%20sheet%20and%20is%20updated%20constantly%2C%20so%20a%20hard%20link%20in%20the%20COUNTIF%20statement%20to%20Column%20L%20will%20be%20inaccurate%20when%20the%20list%20is%20updated%20(and%20reorganized%20when%20resorted%20alphabetically).%3C%2FP%3E%3CP%3EI'd%20like%20COUNTIF%20to%20pickup%20whatever%20item%20is%20in%20the%20adjacent%20Column%20L.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20guys.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20991px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280672iE20587CAF04EC8AC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2353542%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2353565%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Lookup%20for%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2353565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053543%22%20target%3D%22_blank%22%3E%40HasanSheriff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edo%20a%20power%20query%20against%20that%20list%20for%20column%20J%20and%20column%20L%20then%20after%20you%20load%20the%20query%20to%20the%20worksheet%2C%20click%20the%20refresh%20down%20arrow%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1620963090387.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280673i70AF89376C103D94%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1620963090387.png%22%20alt%3D%22Yea_So_0-1620963090387.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20uncheck%20the%20background%20refresh%20setting%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_1-1620963253242.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280675iBEA49B4C1F6198AF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_1-1620963253242.png%22%20alt%3D%22Yea_So_1-1620963253242.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20now%20you%20can%20use%20the%20data%20with%20the%20countif%20without%20worrying%20about%20it%20being%20resorted%20or%20whatever%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2353592%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Lookup%20for%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2353592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20have%20to%20learn%20the%20Power%20Query.%20Wish%20my%20luck!%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All,

 

Wondering if this is possible.

 

The items in Column J and L are a list lookup (auto sorted alphabetically) from another sheet and is updated constantly, so a hard link in the COUNTIF statement to Column L will be inaccurate when the list is updated (and reorganized when resorted alphabetically).

I'd like COUNTIF to pickup whatever item is in the adjacent Column L.

 

Thanks guys.

 

Capture.PNG

 

4 Replies
best response confirmed by HasanSheriff (New Contributor)
Solution

@HasanSheriff 

 

do a power query against that list for column J and column L then after you load the query to the worksheet, click the refresh down arrow:

Yea_So_0-1620963090387.png

 

And uncheck the background refresh setting:

Yea_So_1-1620963253242.png

 

So now you can use the data with the countif without worrying about it being resorted or whatever

@Yea_So 

I'll have to learn the Power Query. Wish my luck!

Thanks!

@Yea_So 

You're a life saver!

Done.PNG

 

It wasn't so painful to learn power query was it? and now you learned something new and an added hammer in your toolbox