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"

A	B
Date	LBS
05/01/18	10
05/01/18	44
05/02/18	50
05/02/18	20
05/03/18	12
05/03/18	42
05/04/18	7
05/05/18	32
05/06/18	22

<tbody>
</tbody>

Report

A	B	C	D	E
05/01/18	05/02/18	05/03/18	05/04/18	05/05/18

<tbody>
</tbody>

=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
1	5/1/2018	5/2/2018	5/3/2018	5/4/2018	5/5/2018
2	54	70	54	7	32

<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>
</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.