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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)

Any ideas?

Originally Posted by mminsf

I'm trying to figure out a cleaner/easier way to do this:

=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!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!

Originally Posted by Domenic

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?

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

Originally Posted by mminsf

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:F100<>"",Sheet1!C1:F100)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Originally Posted by Domenic

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:F100<>"",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!

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!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?
>
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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!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?
>
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

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!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?
>
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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!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?
>
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

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!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?
>
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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!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?
>
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

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!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?
>
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

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!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?
>
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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!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?
>
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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!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?
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

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!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?
>
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>

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(Sheet1!A:A,Sheet2!B11,Sheet1!D:D)
+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!E:E)+SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!F:F)
>
> Any ideas?
>
>

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!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?
>
>

Assuming titles in row 1:

=SUMPRODUCT((Sheet1!A2:A65536=Sheet2!B11)*(Sheet1!C2:F65536))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"mminsf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to figure out a cleaner/easier way to do this:
>
>
=SUMIF(Sheet1!A:A,Sheet2!B11,Sheet1!C:C)+SUMIF(Sheet1!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
>