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