Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF with rows and columns
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. -- To contact me take a davidhowdon and add a @yahoo.co.uk to the end. |
#2
|
|||
|
|||
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. 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 __________________________ |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
SUMIF with rows and columns
On Sat, 15 Oct 2005 10:41:40 -0700, "Peo Sjoblom"
wrote: 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 __________________________ |
#6
|
|||
|
|||
SUMIF with rows and columns
Peo Sjoblom wrote:
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). -- To contact me take a davidhowdon and add a @yahoo.co.uk to the end. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert columns to rows & rows to columns | Excel Discussion (Misc queries) | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
inserting columns within certain rows only | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
sumif columns and rows | Excel Worksheet Functions |