If sumproduct formula - combining from two data sheets

[XWREN]

I am trying to combine these two formulas into one formula pulling information from two separate data sheets (one called contracts, the other called contracts 2) and comparing to the budget. This is the formula that works with one data sheet (contract), but when I try to combine using a different data sheet (contract 2) I receive a "VALUE" error. Can anyone help with combining these formulas please:

=IF($I4>1,0,SUMPRODUCT(--($B4=Contracts!$Z$2:$Z$1244),--($C4=Contracts!$AA$2:$AA$1244),--($D4=Contracts!$AB$2:$AB$1244),--($H4=Contracts!$AC$2:$AC$1244),--( Contracts!$AM$2:$AM$1244="Instructor"),--Contracts!$AE$2:$AE$1244))

=IF($I4>1,0,SUMPRODUCT(--($B4='Contracts2!'!$N$2:$N$464),--($C4='Contracts2'!$K$2:$K$464),--($D12='Contracts2'!$L$2:$L$464),--($H12='Contracts2'!$O$2:$O$464),--( 'Contracts2'!$Z$2:$Z$464="Instructor"),--'Contracts2'!$J$2:$J$464))

Any help is very much appreciated,

Thanks

 

[Dave Patton]

You can use
Formula 1
+
Formula 2

The formula may be quite long.

You can use a cell for the first formula and another cell for
the second formula and add the two cells in a third cell.

Excel 2010
	A	B	C	D
1	(Formula 1+ Formula 2)	Formula may be very long
2
3	0	0	0
4
5a

Cell Formulas
Range	Formula
A3	=0+N("formula 1")
B3	=0+N("Formula 2")
C3	=A3+B3

 

[XWREN]

You can use
Formula 1
+
Formula 2

The formula may be quite long.

You can use a cell for the first formula and another cell for
the second formula and add the two cells in a third cell.
Excel 2010

	A	B	C	D
(Formula 1+ Formula 2)	Formula may be very long

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

5a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=0+N("formula 1")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=0+N("Formula 2")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=A3+B3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Dave, Thanks for this suggestion. I had considered this, but I already have 4 columns as I am pulling the information in by account (we have 4 accounts), so really didn't want to add another 4 columns. We have gone to a new system and I am using two different data sets. If I can't combine it, I will certainly use this suggestion. Many thanks.

 

[Dave Patton]

My first suggestion could be similar to one of the following; use your 2 distinct SumProduct formulas.

Excel 2010
	A	B
4	50	50
5a

Cell Formulas
Range	Formula
A4	=IF($I4>1,0,SUMPRODUCT(--(C4:C12>10),D4:D12))+IF($I4>1,0,SUMPRODUCT(--(Z4:Z100>10),C4:C100))
B4	=IF($I4>1,0,(SUMPRODUCT(--(C4:C12>10),D4:D12))+SUMPRODUCT(--(Z4:Z100>10),C4:C100))

Named Ranges
Name	Refers To	Cells
z	=zList!$A$2

 

[XWREN]

Hi Dave,

Fantastic - works like a charm. Many thanks.