SUMIFS using dynamic array constant

**lsole** · 03-31-2021, 10:08 AM

Hello,

This is my first post on this forum. Thank you all for your help!

I'm trying to sum data in a cell when data in another sheet meet some criteria. The formula I'm using, which is working, is the following:

=SUM(SUMIFS(Data!$A:$A;Data!$B:$B;MONTH(B$1);Data!$C:$C;{"203*";"204*";"206*";"280*"}))

This formula I want to copy-paste in many other cells but I would like to avoid manually updating the array constant if the values change later. This I could do it with a named range (via Define name) but it is not straightforward to see which values are being used when there are many rows. I would prefer to have a column with cells containing these values for each row and somehow having them converted to an array constant. Is this possible?

I would prefer a solution which does not use VisualBasic as other users will use this file.

Thank you in advance!

Llorenc

**AliGW** · 03-31-2021, 10:11 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

**XLent** · 03-31-2021, 10:27 AM

so, if you're saying you want to store 203*;204*;206*;280* in a cell then you could use something like below, where $C2 holds the delimited criterion

Formula:

B2:

=SUM(SUMIFS(Data!$A:$A;Data!$B:$B;MONTH(B$1);Data!$C:$C;TRIM(MID(SUBSTITUTE($C2;";";REPT(" ";100));1+100*(ROW(A$1:INDEX(A:A;($C2<>"")+LEN($C2)-LEN(SUBSTITUTE($C2;";";""))))-1);100))))

copied down

in summary, in the above, you're not creating an inline array but rather you're creating an Array of criteria by splitting the delimited string into 1 to n individual elements
if you modify the string in C2 so the result in B2 should update accordingly... the substitute etc is a bit convoluted as you try to ensure the array of criteria is only as big as the number of elements in the delimited string.

edit: adding a worked example to illustrate, in case helpful.

**lsole** · 03-31-2021, 12:42 PM

Hello,

I don't get how this formula exactly works but it does, thank you very much!

I suppose it parses the string and outputs an array. I will have to investigate this use of row() and index().

Llorenc

SUMIFS using dynamic array constant

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

SUMIFS using dynamic array constant

Re: SUMIFS using dynamic array constant

Re: SUMIFS using dynamic array constant

Re: SUMIFS using dynamic array constant

Thread Information

Users Browsing this Thread

Similar Threads

SUMPRODUCT/SUMIFS Dynamic array doesn't works

[SOLVED] SUMIFS with multiple criteria but only with one constant ARRAY?

No VBA method to create a dynamic array constant

[SOLVED] Determining dynamic array length on a constant number of observations

SUMIFS with Array Constant Fails

[SOLVED] Dynamic Array, Index & Match, SUMIFS Formula Help!

Constant Array?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions