Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumif across columns
I'm trying to figure out a cleaner/easier way to do this: =SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(She et1!A:A,Sheet2!B11,Sheet1!D:D)+SUMIF(Sheet1!A:A,Sh eet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,S heet1!F:F) Any ideas? -- mminsf ------------------------------------------------------------------------ mminsf's Profile: http://www.excelforum.com/member.php...o&userid=24767 View this thread: http://www.excelforum.com/showthread...hreadid=383325 |
#2
|
|||
|
|||
You could try
=SUMPRODUCT(--(Sheet1!A1:A1000Sheet2!B11),Sheet1!B1:B1000+Sheet1 !C1:C1000+Sh eet1!D1:D1000+Sheet1!E1:E1000+Sheet1!F1:F1000) but it doesn't look that much simpler to me. -- HTH Bob Phillips "mminsf" wrote in message ... I'm trying to figure out a cleaner/easier way to do this: =SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(She et1!A:A,Sheet2!B11,Sheet1! D:D)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF (Sheet1!A:A,Sheet2!B11,She et1!F:F) Any ideas? -- mminsf ------------------------------------------------------------------------ mminsf's Profile: http://www.excelforum.com/member.php...o&userid=24767 View this thread: http://www.excelforum.com/showthread...hreadid=383325 |
#3
|
|||
|
|||
mminsf Wrote: I'm trying to figure out a cleaner/easier way to do this: =SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(She et1!A:A,Sheet2!B11,Sheet1!D:D)+SUMIF(Sheet1!A:A,Sh eet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,S heet1!F:F) Any ideas? Try... =SUMPRODUCT((Sheet1!A1:A100=Sheet2!B11)*Sheet1!C1: F100) Adjust the range accordingly. Note that SUMPRODUCT does not accept whole column references. But you can use 'near' whole column references, such as A2:A65536. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=383325 |
#4
|
|||
|
|||
Sum C to F in G per record, then invoke:
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!G:G) mminsf wrote: I'm trying to figure out a cleaner/easier way to do this: =SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(She et1!A:A,Sheet2!B11,Sheet1!D:D) +SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(She et1!A:A,Sheet2!B11,Sheet1!F:F) Any ideas? |
#5
|
|||
|
|||
Domenic Wrote: Try... =SUMPRODUCT((Sheet1!A1:A100=Sheet2!B11)*Sheet1!C1: F100) thanks so much for your quick reply! I gave it a try, but I'm getting a #value! error. any thoughts on how I might fix that? -- mminsf ------------------------------------------------------------------------ mminsf's Profile: http://www.excelforum.com/member.php...o&userid=24767 View this thread: http://www.excelforum.com/showthread...hreadid=383325 |
#6
|
|||
|
|||
That happens because you have text values like "" in ranges to sum.
So: 7 * "" or 7 + "" will error out. mminsf wrote: Domenic Wrote: Try... =SUMPRODUCT((Sheet1!A1:A100=Sheet2!B11)*Sheet1!C 1:F100) thanks so much for your quick reply! I gave it a try, but I'm getting a #value! error. any thoughts on how I might fix that? |
#7
|
|||
|
|||
Assuming titles in row 1:
=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1! C2:F65536)) -- HTH Sandy Replace@mailinator with @tiscali.co.uk "mminsf" wrote in message ... I'm trying to figure out a cleaner/easier way to do this: =SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(She et1!A:A,Sheet2!B11,Sheet1! D:D)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF (Sheet1!A:A,Sheet2!B11,She et1!F:F) Any ideas? -- mminsf ------------------------------------------------------------------------ mminsf's Profile: http://www.excelforum.com/member.php...o&userid=24767 View this thread: http://www.excelforum.com/showthread...hreadid=383325 |
#8
|
|||
|
|||
mminsf Wrote: thanks so much for your quick reply! I gave it a try, but I'm getting a #value! error. any thoughts on how I might fix that? Aladin has just pointed out that that happens because you have values like "" in the ranges to sum (his post to follow -- thanks Aladin, I hadn't thought about that :)). Acordingly, try the following formula... =SUM(IF(Sheet1!A1:A100=Sheet2!B11,IF(Sheet1!C1:F10 0<"",Sheet1!C1:F100))) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=383325 |
#9
|
|||
|
|||
Domenic Wrote: Aladin has just pointed out that that happens because you have values like "" in the ranges to sum (his post to follow -- thanks Aladin, I hadn't thought about that :)). Acordingly, try the following formula... =SUM(IF(Sheet1!A1:A100=Sheet2!B11,IF(Sheet1!C1:F10 0<"",Sheet1!C1:F100))) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! you're my new best friend -- thanks so much, it worked perfectly! -- mminsf ------------------------------------------------------------------------ mminsf's Profile: http://www.excelforum.com/member.php...o&userid=24767 View this thread: http://www.excelforum.com/showthread...hreadid=383325 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF using two columns in both Range and Criteria | Excel Discussion (Misc queries) | |||
SUMIF using two columns in both Range and Criteria | Excel Worksheet Functions | |||
SUMIF over multiple columns | Excel Worksheet Functions | |||
Sumif over multiple columns | Excel Worksheet Functions | |||
sumif columns and rows | Excel Worksheet Functions |