Search results

I am wondering, though, whether it is column C and not column A that you really want to consider?

Or, if you mean not to consider column B: =AND(G2<>"",G2<=References!$E$2,H2<>"Yes",I2<>"Lost",A2=FALSE)

Try this: =AND(G2<>"",G2<=References!$E$2,B2="No",H2<>"Yes",I2<>"Lost",A2=FALSE)

Why and under what circumstances would the attempts to pass the exam not be dated or at least numbered? I don't think this is a realistic scenario. Yes - for each person. You should know how to look at parts of a complex formula and run them to see what they do. Here, run f. For example...

If you need a solution for Excel 2021, then you'll need to find a way around HSTACK, which won't be available to you. Maybe this: =LET(f,FILTER(A2:D9,C2:C9=MAXIFS(C2:C9,A2:A9,A2:A9,B2:B9,B2:B9)), SORT(CHOOSE({1,2},INDEX(f,,1)&" "&INDEX(f,,2),INDEX(f,,4)))) Also posted on ExcelForum.

I'm not quite sure what the question is here, but if you are wanting only the latest result for each individual, you can use this: =LET(f,FILTER(A2:D9,C2:C9=MAXIFS(C2:C9,A2:A9,A2:A9,B2:B9,B2:B9)), SORT(HSTACK(INDEX(f,,1)&" "&INDEX(f,,2),INDEX(f,,4))))

HOW TO ATTACH YOUR SAMPLE WORKBOOK: Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands) and some manually calculated results. Screenshots are of little practical use...

Can't you use the attachment button instead? Nobody really wants to follow an external link. The button will be at the foot of the reply window.

No sample workbook attached, so here's a DIY option in the hope that you have Excel 365 (you don't say):

What do you mean by 'neater'? What doesn't it do that you want it to do?

In F8 copied across: =SUMPRODUCT((MONTH($C$3:$C$227)=MONTH($E8))*(YEAR($C$3:$C$227)=YEAR($E8))*($B$3:$B$227=F$2)) If you have 365, there may be a SPILL formula, but you don't say.

If you have 365 2024, in H2 followed by ENTER: =LET(d,TOCOL(B4:B13&"|"&TRANSPOSE(B4:B13)), h,HSTACK(TEXTBEFORE(d,"|"),TEXTAFTER(d,"|")), FILTER(h,INDEX(h,,1)<>INDEX(h,,2))) Or this: =LET(d,TOCOL(B4:B13&"|"&TRANSPOSE(B4:B13)), h,HSTACK(TEXTBEFORE(d,"|"),TEXTAFTER(d,"|"))...

I was going to look at that route, but then got side-tracked by other things! :DD

You're welcome! Please don't forget to hit the LIKE button under my post. Thanks. :DD

First, get rid of ALL of the INDEX MATCH formulae (they are clunky and slow). We'll use 365 functions instead. Next, in D10 copied across: =LET(d,TOCOL(FILTER('RepHunter Contacts - Member #12'!$BI2:.$DW5000,('RepHunter Contacts - Member #12'!$C2:.$C5000=D$2)),1)...

I am not sure that I understand. In the workbook you have an INDEX MATCH formula that lists US states AND overseas territories (Sheet 1) - is this where you want a new formula? And if it is, and you want the states to align with the states as listed in B and C (is this correct?), where should...

Please ... ;)

Which version of Excel are you using? Where should overseas territories be listed?

Please provide the workbook - we cannot manipulate screenshots.

Please feel free to use the LIKE button under our posts.

Threse are things you need to mention at the outset.

I'll assume you have 365. In H3: C In I3: 07/12/2025 In J3 followed by ENTER: =DROP(GROUPBY(HSTACK(B4:B15,C4:C15),HSTACK(D4:D15,E4:E15,F4:F15),SUM,,0,,(B4:B15=H3)*(C4:C15=I3)),,2) AliGW on MS365 Beta Channel (Windows 11) 64 bit HIJKL 2DomainDatePassFailBlocked 3C12/07/2025242 Sheet: Sheet1

You need SUMPRODUCT, not INDEX MATCH MATCH. In C17 copied across and down: =SUMPRODUCT($F$3:$K$12*($F$1:$K$1=$B17)*($D$3:$D$12=C$16))

You can't do calculations based on cell colour. Do the numeric values appear in the cells? You'd need to use values to work out the average and then colour the cell accordingly. Best to attach a sample workbook.

Please attach the workbook.