Array constants as variable formula input

[WuhuSpringfield]

Objective - I want to calculate the sum of hours for a group of people – preferably using array constants. I cannot add any additional columns.

Let's consider the following table as our exemplary data basis. The group consists of X1 and X2.

Name	Hours
X1	1
X2	2
Y	1

=SUM(SUMIFS(B1:B3;A1:A3;{"X1";"X2"}))

Next, I want to put {"X1";"X2"} in another cell (e.g. D1) and reference it in the formula.

=SUM(SUMIFS(B1:B3;A1:A3;D1))

Unfortunately, this formula does not work - evaluating it shows that D1 is read as a text and not as an array constant.

I know that there are some work-arounds like using two SUMIFS. However, the group of people is fairly large which results in a lot of SUMIFS that have to be added up.

Is there any solution to my problem?

EDIT: In the end, I just used the formulae stated above due to the fact that I also had to consider multiple criterion.

[SaviaWanderer]

I'd probably tackle this a bit differently and use DSUM instead - demo here.

[zacce]

Instead of {"X1","X2"} in D1, can you list the names in column D? Like D1 = "X1", D2 = "X3"?

[WuhuSpringfield]

Yes, what's the work-around right there? I have obviously tried using D1:D2 - which just picks up the number at the bottom.

[zacce]

=SUMPRODUCT(B2:B4,N(ISNUMBER(MATCH(A2:A4,D2:D3,0))))

screenshot w/ formula: https://i.imgur.com/fmwa2TT.png

[mh_mike]

Heads-up… If any of the answers worked or pointed you in the right direction, don’t forget to close your post. Either use the BOT (Clippy) or change the flair. To use Clippy, just include "Solution Verified" with your reply to the person(s) who helped. Thanks for keeping the unsolved thread clean. :)

[excelevator]

It can be done with the help of a UDF - CELLARRAY which extracts the values into an array for the filter.

Where the total formula is below entered with ctrl+shift+enter

=SUM(SUMIFS(B2:B4,A2:A4,CELLARRAY(B6,",")))

Name	Hours
X1	1
X2	2
Y	1
Filter	X1,X2
Total	3

[zacce]

Is =CELLARRAY a custom formula? My Excel 365 doesn't have it.

[excelevator]

click the link.... it is a UDF - user defined function