excel including a dynamic array of constants in a formula

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

excel including a dynamic array of constants in a formula

Post by WebGenii »

I am building a formula to sum the last N entries that match a criteria on a list.
=SUMPRODUCT((A2:A23="joe")*(ROW(2:23)=LARGE((A2:A23="joe")*ROW(2:23),{1,2,3}))*B2:B23)
I have tweaked it to refer to the cell $f$2 where the last row is calculated.
=SUMPRODUCT((INDIRECT("A2:A"&$F$2)="joe")*(ROW(INDIRECT("2:"&$F$2))=LARGE((INDIRECT("A2:A"&$F$2)="joe")*ROW(INDIRECT("2:"&$F$2)),{1,2,3}))*INDIRECT("B2:B"&$F$2))

But I know that the array constant in the LARGE function in Excel is going to haunt me, people will want to change the number of entries dynamically.
Ideally, I'd like to be able to put in a ROW(INDIRECT("1:"&cell ref)) in place of {1,2,3}, however this doesn't work.
Even when I stare at it sternly.

I've tried a name range holding the constant, and that works - but cell references aren't accepted. So that doesn't get me any further along.
I've tried turning the ROW(INDIRECT("1:"&cell ref)) into a named range, but this fails with the N/A error when the LARGE function is applied to the previous ROW(INDIRECT.

Any suggestions on functions, syntax or general approach?
TIA
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: excel including a dynamic array of constants in a formula

Post by HansV »

See the attached version. I created two dynamic named ranges: MyNames is A2:Am and MyValues is B2:Bm where m is the last used row.
The name you want to look for is in E7, the number of occurrences you want to count is in E8, and the sum in E9 is given by the following array formula confirmed with Ctrl+Shift+Enter. It is based on a formula by Aladin Akyurek.

=SUM(IF(ROW(MyValues)>=LARGE(IF(MyNames=E7,IF(ISNUMBER(MyValues),ROW(MyValues))),MIN(E8,COUNTIFS(MyNames,E7))),IF(MyNames=E7,IF(ISNUMBER(MyValues),MyValues))))

sample of joes.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: excel including a dynamic array of constants in a formula

Post by rory »

Which version of Excel does it need to work with?
Regards,
Rory

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: excel including a dynamic array of constants in a formula

Post by WebGenii »

Good catch Rory, I forgot to mention that - Excel 2013 and above.

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: excel including a dynamic array of constants in a formula

Post by WebGenii »

HANS you are amazing. I am going to think about this now ;)

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: excel including a dynamic array of constants in a formula

Post by HansV »

Microsoft 365 has a new function FILTER that could be used, but that won't work in Excel 2013, 2016 and 2019 (the latter in the perpetual license version of Office)
Best wishes,
Hans

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: excel including a dynamic array of constants in a formula

Post by WebGenii »

Things are always more challenging when multiple versions of office are in use :cheers:

I thought I'd share a couple of pieces of the formatting I used in the final spreadsheet, just in case someone is reading this thread looking for inspiration.
The actual spreadsheet is being used for data collection, the users want to use some N number of entries to establish the baseline values. That will be the label used - not "joe"
I wanted to mark where the baseline entry collection started AND which entries are being used as baseline values. So I did what you see in the attached spreadsheet. I ended up using hidden columns to accomplish this, instead of my normal preference for a range formula. Even though the BaselineStart formula works in cell, it doesn't work when used in conditional formatting. (This was in Office 365, so is it a glitch or who knows). Anyhow, I needed to get this out the door, so hidden columns it is.

Thanks again for the help.
You do not have the required permissions to view the files attached to this post.

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: excel including a dynamic array of constants in a formula

Post by WebGenii »

They loved it so much, that now they want to chart it...

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: excel including a dynamic array of constants in a formula

Post by HansV »

What do they want to chart?
Best wishes,
Hans