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

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

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

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?

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

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?

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

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

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