SUMIF with rows and columns

[David Howdon]

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.
Is this

A) a bug
B) a (rather silly) feature
C) (most likely) me making a stupid mistake in typing the formulae and
not being able to spot it.

Excel 2003 (11.6355.6568) SP1 in case that helps.

 

[Richard Buttrey]

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.
Is this

A) a bug
B) a (rather silly) feature
C) (most likely) me making a stupid mistake in typing the formulae and
not being able to spot it.

Excel 2003 (11.6355.6568) SP1 in case that helps.

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.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[David Howdon]

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.

 

[Peo Sjoblom]

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)

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.

 

[Richard Buttrey]

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)))

I like it. That's a new one for me.

Thanks Peo

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[David Howdon]

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)))

Thanks that worked perfectly (plus taught me something new about Excel).