CODEWITHSUNDEEP
arraysexcelif-statementliterals
lostinOracle
lostinOracle

Reputation: 408

Excel - sumifs dynamic array literal

=SUM(SUMIFS('Output'!$H$50:$H$69,'Output'!$C$50:$C$69,{"*PLoan","Deficit Loan"},'Output'!$G$50:$G$69,X97:AC97))

I have the above array formula. X97:AC97 is where I have my array literal and the values in this array literal are conditioned on some other parameters (let's say the values are a,b,c,d,e if the conditions are met). Because the values are conditional, it might show up as a "" "" "" e "". So in the sumifs array formula it takes the array literal as values {"a","","","","e",""}, which causes an error. How do I make it so that the array is {"a","e"}?

edit: I realized after much effort that sumifs might not be the correct method as it requires alignment. For example, I might have a (1,0) criteria met from {PLoan, Deficit Loan} while X97:AC97 = (0,1,0,0,0,0). I do want the sum for that number but it won't sum due to the mismatch.

I got the below to work. If anyone can think of a way to incorporate wildcards as part of my string search, let me know...

=SUMPRODUCT(IFERROR(ISNUMBER(MATCH('Output'!$G$50:$G$69,$X$97:$AC$97,0)*MATCH('Output'!$C$50:$C$69,{"PLoan","Operating Deficit Loan"},0))*('Output'!$H$50:$H$69),0))

Upvotes: 1

Views: 652

Answers (1)

XOR LX
XOR LX

Reputation: 7742

COUNTIF(S)/SUMIF(S) do not necessarily "require alignment" of the criteria being passed. It all depends upon what it is you wish to count.

If you require a count which comprises all combinations of the two sets of criteria, then it is sufficient that the two arrays containing those criteria be orthogonal.

At the moment, both of the criteria arrays which you are passing are of the same vector-type, i.e. row-vectors, since:

X97:AC97

is clearly a row- (horizontal) vector, as also is:

{"*PLoan","Deficit Loan"}

since, for English-language versions of Excel, the comma and semicolon represent, respectively, separators within row (horizontal) and column (vertical) array constants.

As such, in your case you simply need to transpose one of these two arrays, viz:

=SUM(SUMIFS(Output!$H$50:$H$69,Output!$C$50:$C$69,{"*PLoan";"Deficit Loan"},Output!$G$50:$G$69,X97:AC97))

(We could also transpose the other, viz:

=SUM(SUMIFS(Output!$H$50:$H$69,Output!$C$50:$C$69,{"*PLoan","Deficit Loan"},Output!$G$50:$G$69,TRANSPOSE(X97:AC97)))

or else move the entries within X97:AC97 to some other, vertical, range.)

See here for more information.

Regards

Upvotes: 2

Related Questions