Array formula SUMIF with 2D sum_range array

[Rich_84]

Hi,

Supposing I have the following data in A14,


Tag Jan Feb Mar
x 1 4 3
x 2 1 6
y 4 5 9


Would it be possible to have array formula in B55 which will do the
subtotals for "x" (giving {3,5,9} as a result)?

The reason I want to do this as array formula is so I can perform this
calculation in VBA (1000's of times on a bigger data range) where the
resulting arrays can be easily stored in a variant array (using the
Evaluate() method). I was thinking of something along the lines of:

{=SUMIF(A2:A4,"x",B24)}

but this doesn't seem to be what I'm looking for.

Thanks,

Richard

 

[Bernard Liengme]

Use =SUMPRODUCT((A2:A4="x")*B24)
But if you are using VBA, why not code the operation to find the sum?
best wishes

 

[Rich_84]

Thanks for the reply, I tried your suggestion but that gave me the value 17
in B55, ideally I want the resulting array to be {3,5,9}

As to why I want do it this way, it seems faster to do these sort of
calculations the "excel way" and then store to an array in VBA rather then do
it by looping through arrays in all in VBA.

Using e.g.
MyArray = Evaluate("A2:A4+B2:B4")

seems faster than having 2 arrays in VBA and adding them together by looping
through the elements. Anyhow, I'm pretty new to this so any suggestions are
appreciated!

Thanks,

Richard

 

[Bernard Liengme]

I think we would be more able to help if we knew the purpose of getting the
array.
best wishes