User Profile
ColinJHarrison
Brass Contributor
Joined Oct 03, 2022
User Widgets
Recent Discussions
Re: COUNTIFS problem
Ellbeardo Hi Ellbeardo, If I've understood you correctly, you have a data set like the one I've shown in the attached image (your column T is my column A, and your W is my B), in which Column A contains various values, including "Other" and "Temporary", against which, in column B, there can be various values, but you want to count only the pairs where A= either "Other" or "Temporary" and B=0 (which I have highlighted yellow in the image.) You could do this by summing 2 COUNTIFS() calls like this: =COUNTIFS(A1:A19,"Other",B1:B19,0)+COUNTIFS(A1:A19,"Temporary",B1:B19,0) or like this (same thing really) =SUM(COUNTIFS(A1:A19,"Other",B1:B19,0),COUNTIFS(A1:A19,"Temporary",B1:B19,0)) Hope that helps 🙂 ColinRe: Passing a Table as a variable (in a LAMBDA() function)
Riny_van_Eekelen Hi Riny (are you the only person on this forum? Haha) I had tried INDIRECT(), but it doesn't give me the expected result. If I have a table called Table1, and I call =Table1[#Headers], (as I have in cell D10 in the attached image/sheet) then I get the header row values spilling horizontally from D10, as I would expect. I would have thought that calling =LAMBDA(Tn,INDIRECT(Tn&"[#Headers]"))(Table1) should do exactly the same thing, but it doesn't. What I get is a spill range the same dimensions as the whole Table, filled with #REF! errors, which are "Invalid Cell Reference" errors. See the screenshot below, in which D4 contains the LAMBDA() call shown in red in D2. Just in case it was an issue with the LAMBDA() test syntax, I assigned the function to a name (TPX()) in the Name Manager and called it directly from cell G4 (=TPX(Table1)), which as you can see, has exactly the same result. I am perplexed. I have attached the sheet below. Cheers, ColinRe: Data Validation drop down list
agkrolak Hi agkrolak, First you will need to create a list of all your Code-Definition pairs. This can be on a separate sheet. Next I would declare each column of that list as a Named Range (call one Codes and the other Definitions). If the list will never change length, you can do this just by selecting the whole range in the column (not the whole column - just the cells that contain data) and typing the new name into the Name Box (I'm assuming you know where that is - if not let me know). If the list will change length, you will need to declare dynamic Named Ranges, using either OFFSET() or INDEX() - here's a great tutorial: https://exceljet.net/glossary/dynamic-named-range Now, for your Code drop-down cell, go to Data/Data Validation, and in the Allow drop-down, select List, then in Source put =Codes. For the Definiton cell, use a lookup function like XLOOKUP(), or INDEX(MATCH()). The function call will be something like this (if your Code drop-down is cell A1). =XLOOKUP(A1,Codes,Definitions,"Code not found",0) The "Code not found" error should actually be completely redundant, because your dropdown list is defined on the same data as the lookup, so in theory it can't fail! But I always like to put in a fail message... force of habit! Hope that works for you! Colin
