You can use this formula as long as the ranges are equally sized

=SUM((B34:M34=1)*(TRANSPOSE(B3:B14)))

entered with ctrl + shift & enter

note that the condition =1 looks for a numeric 1 while your sumif looked for
a text value, if indeed they are text values use

=SUM((B34:M34="1")*(TRANSPOSE(B3:B14)))

--
Regards,

Peo Sjoblom

(No private emails please)


"David Howdon" wrote in
message ...
Richard Buttrey wrote:
On Thu, 13 Oct 2005 19:18:23 GMT, David Howdon
wrote:


I tried to set up the following SUMIF formula
=SUMIF(B34:M34,"=1",B3:B14)

i.e. the criteria were in a row and the summands were in a column.

However what excel actually did was to sum cells in the *row* starting

from B3 the corresponded to the criteria.

[snip]


Hmm, you seem to be mixing a row and column range. I'm fairly certain
that Excel doesn't allow this in SumIF or other array formulae.

Copy the range B34:M34 and then Edit PasteSpecial Values Transpose
into A3.

Now the formula =SUMIF (A3:A14,"=1",B3:B14) should work.


Thanks for that it would work for this one example.
However the reason I was using the formula was so I could copy and paste
into various other rows and have it continue to look up correctly. Simply
transposing the source data array would mean that this does work as
instead of using row B35:M35 when I copy the formula one cell down (as I
want it to) it would try to look at A4:A15 which is not what I want.

But if Excel cannot work with rows and columns in SUMIF I'll just have to
redesign what I've done - or do all the formulae manually.

--
To contact me take a davidhowdon and add a @yahoo.co.uk to the end.