Array formula SUMIF with 2D sum_range array

  • Thread starter Thread starter Rich_84
  • Start date Start date
R

Rich_84

Hi,

Supposing I have the following data in A1:D4,


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 B5:D5 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",B2:D4)}

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

Thanks,

Richard
 
Use =SUMPRODUCT((A2:A4="x")*B2:D4)
But if you are using VBA, why not code the operation to find the sum?
best wishes
 
Thanks for the reply, I tried your suggestion but that gave me the value 17
in B5:D5, 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
 
I think we would be more able to help if we knew the purpose of getting the
array.
best wishes
 
Back
Top