sumproduct issue

[bigry26]

Hi,

I have some data that I need to reference from a different sheet while its closed. I know I need to use sumproduct instead of sumifs but I can't seem to get it to work. I need to total the number of lbs for each day.



"Raw Data"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]LBS[/TD]
[/TR]
[TR]
[TD]05/01/18[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]05/01/18[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]05/02/18[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]05/02/18[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]05/03/18[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]05/03/18[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]05/04/18[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]05/05/18[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]05/06/18[/TD]
[TD]22[/TD]
[/TR]
</tbody>[/TABLE]



Report
[TABLE="width: 125"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]05/01/18[/TD]
[TD]05/02/18[/TD]
[TD]05/03/18[/TD]
[TD]05/04/18[/TD]
[TD]05/05/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


=sumproduct(--($A:$A),--($B:$B,A2) Is the formula I'm using and I am getting #value error. I am using excel 2016.


Thanks in advance,

Big Ry

 

[steve the fish]

Firstly it looks like you have the columns the wrong way round. Secondly in this case you only use the -- to coerce a true/false result into a number so:

=SUMPRODUCT(--(A:A=A2),B:B)

Then really you shouldnt use full column references in a SUMPRODUCT so change them to a sensible last row. You were getting the value error because you were trying to coerce the header into a number.

 

[JoeMo]

In this example your data are on sheet3 and the results you want on sheet4.

Excel Workbook
	A	B	C	D	E
1	5/1/2018	5/2/2018	5/3/2018	5/4/2018	5/5/2018
2	54	70	54	7	32
Sheet4

 

[bigry26]

In this example your data are on sheet3 and the results you want on sheet4.
Sheet4

	A	B	C	D	E

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]5/2/2018[/TD]
[TD="align: right"]5/3/2018[/TD]
[TD="align: right"]5/4/2018[/TD]
[TD="align: right"]5/5/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]32[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 =SUMPRODUCT(--(Sheet3!$A$2:$A$10=Sheet4!A$1),Sheet3!$B$2:$B$10) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you Joe Mo!

 

[JoeMo]

You are welcome - thanks for the reply.