User Profile
Jos_Woolley
Iron Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Re: How to Look for the Most Occurring Word in a 5x10 Table
KuroShiiro21 Hi, Office 365 or Excel 2019: =INDEX(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",0,K3:M16)&"</b></a>","//b"),MATCH(1,0/FREQUENCY(0,1/(1+COUNTIFS(K3:M16,K3:M16,K3:M16,"<>"))))) Excel 2007 or later: =INDEX(K3:M16,1+INT((MATCH(1,0/FREQUENCY(0,1/(1+COUNTIFS(K3:M16,K3:M16,K3:M16,"<>"))))-1)/COLUMNS(K3:M16)),1+MOD(MATCH(1,0/FREQUENCY(0,1/(1+COUNTIFS(K3:M16,K3:M16,K3:M16,"<>"))))-1,COLUMNS(K3:M16))) Regards1.4KViews0likes0CommentsRe: Sorting & Counting with multiple criteria
Hi, Using your attached workbook and assuming you put your choice of PRI INS ID in O2, then, in P2: 1) If you have access to the Office 365 functions UNIQUE and FILTER: =COUNT(UNIQUE(FILTER(H$2:H$1000,F$2:F$1000=O2))) 2) Otherwise, array formula (CRTL+SHIFT+ENTER): =SUM(IF(FREQUENCY(IF(F$2:F$1000=O2,H$2:H$1000),H$2:H$1000),1)) Copy down to give similar results for PRI INS ID entries in O3, O4, etc. The one difference in results generated by these two set-ups is for PRI INS ID 127679. There is one blank entry in the CLAIM ID column for this PRI INS ID: of the two solutions given above, 1) excludes this result, 2) includes it. If 2) is the only option for you and you don't wish blank CLAIM IDs to be included then use instead: =SUM(IF(FREQUENCY(IF(F$2:F$1000=O2,IF(H$2:H$1000<>"",H$2:H$1000)),H$2:H$1000),1)) Regards1.5KViews0likes0CommentsRe: Sum of Range based on numbers in a different row
Detlef_Lewin Then you must have amended the original values as given by the OP, since it will only be by a very fortunate coincidence that those two formulas produce the same result: SUM sums the arguments passed to it, SUMPRODUCT sums the products of the respective entries in the arguments passed to it. Regards1.5KViews0likes2CommentsRe: Exel
mathetes "the act of sorting moves the data but not the reference. That's true even if you were using a named range." Not if the reference includes the sheet name. In fact, one of my top Excel 'pet peeves' is the fact that, when entering a formula which includes a reference to another sheet, upon switching back to the sheet in which the formula resides, the default behaviour is to now include the sheet name within any references to that sheet. And this, if unnoticed, can cause undesirable results when sorting. Regards1.3KViews0likes2Comments- 2.2KViews0likes0Comments
Re: Return value based on multiple critieria
SergeiBaklan I'm afraid that's simply not correct. I'm not referring to MATCH; even before the formula arrives at the MATCH part it has to process this product of arrays: (Data!$E:$E<=B$1)*(Data!$F:$F>=B$1)*(Data!$A:$A=$A2) and I assure you that every single one of the 3,145,728 cells referenced within Data!$A:$A, Data!$E:$E and Data!$F:$F will be processed, irrespective of whether they are empty or beyond the last-used cell in those ranges and also independent of the MATCH function to which they are being passed. Regards2.2KViews0likes0CommentsRe: Return value based on multiple critieria
hynguyen The technique of inserting one or more INDEX functions at certain positions within a formula so as to avoid pressing CTRL+SHIFT+ENTER does not at all mean that that formula is not processed as an array formula. If anything, the non-CSE INDEX construction is less efficient than the equivalent CSE one; after all, an extra function call is an extra function call... Since such constructions are processed in exactly the same manner as the CSE version, the use of entire column references is to be avoided. The following formula, provided by SergeiBaklan: =INDEX(Data!$D:$D,MATCH(1,INDEX( (Data!$E:$E<=B$1)*(Data!$F:$F>=B$1)*(Data!$A:$A=$A2),0),0)) is having to process more than 3 million cells, an astonishing number for a single formula. And that's just for one instance of that formula; I can imagine that if you had just a few hundred or so of the above then your spreadsheet would practically come to a halt. Reduce the upper row being referenced to a suitably low, though sufficient, value. Better still, use either table ranges (which automatically detect the last-used cell within the range) or else define your own dynamic ranges. Regards2.3KViews1like4CommentsRe: EXCEL FORMULA HELP
IngeborgHawighorst Perhaps the OP requires that the file in question be workable in the desktop version of Excel. I have Office 365, though functions such as UNIQUE, FILTER, etc. have not yet been rolled out as part of my subscription; as such, I have to use (and remain in) Excel Online when I want to employ those functions. Regards1.9KViews0likes0CommentsRe: EXCEL FORMULA HELP
hurshie Thanks. I think you mean: =SUM(IF(FREQUENCY(IF(C$2:C$11=1033,IF(B$2:B$11="Hourly",MATCH(A$2:A$11&"",A$2:A$11&"",0))),ROW(A$2:A$11)-MIN(ROW(A$2:A$11))+1),1)) Change the hard-coded values (1033 and "Hourly") in the above to actual cell references for flexibility. And remember that the above is an array formula. Regards1.9KViews0likes2CommentsRe: EXCEL FORMULA HELP
hurshie =SUMPRODUCT(0+(B$2:B$11=D2),1/COUNTIFS(A$2:A$11,A$2:A$11&"",B$2:B$11,B$2:B$11&"")) =SUM(IF(FREQUENCY(IF(B$2:B$11=D2,MATCH(A$2:A$11&"",A$2:A$11&"",0)),ROW(A$2:A$11)-MIN(ROW(A$2:A$11))+1),1)) Both of these will handle blank cells within the range A2:B11. Regards1.9KViews0likes8Comments
Recent Blog Articles
No content to show