Array formula SUMIF with 2D sum_range array
 

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

Array formula SUMIF with 2D sum_range array
 

Use =SUMPRODUCT((A2:A4="x")*B2:D4)
But if you are using VBA, why not code the operation to find the sum?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rich_84" wrote in message
...
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

Array formula SUMIF with 2D sum_range array
 

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

"Bernard Liengme" wrote:

Use =SUMPRODUCT((A2:A4="x")*B2:D4)
But if you are using VBA, why not code the operation to find the sum?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rich_84" wrote in message
...
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

Array formula SUMIF with 2D sum_range array
 

I think we would be more able to help if we knew the purpose of getting the
array.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rich_84" wrote in message
...
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

"Bernard Liengme" wrote:

Use =SUMPRODUCT((A2:A4="x")*B2:D4)
But if you are using VBA, why not code the operation to find the sum?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rich_84" wrote in message
...
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