Sumproduct Help

[karldugan]

Hello Excelers,

I think a sumproduct is the solution but honestly I never understand how they work If sumproduct does work, would you mind explaining how the formula is built/how it is working?

I want to add up stage 1 - 4 if it is between a certain date, say 01/09/2022 and 30/09/2022. I could use a repeating sumifs, but I'm sure I've seen sumproduct do the same thing better.

Thanks in advance - Karl.

Stage 1	Stage 2	Stage 3	Stage 4	Stage 1 Date	Stage 2 Date	Stage 3 Date	Stage 4 Date
570​	3180​	9073​	1772​	07/09/2022​	07/10/2022​	09/11/2022​	05/12/2022​
1834​	5458​	5469​	7875​	12/03/2022​	07/04/2022​	07/05/2022​	13/06/2022​
4294​	8165​	8477​	2701​	30/07/2022​	30/08/2022​	21/09/2022​	08/10/2022​
3019​	9472​	2569​	7433​	14/04/2022​	16/05/2022​	30/05/2022​	05/07/2022​
9662​	5729​	7187​	7461​	19/04/2022​	10/05/2022​	11/06/2022​	14/07/2022​

 

[Domenic]

Is this what you mean?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Stage 1	Stage 2	Stage 3	Stage 4	Stage 1 Date	Stage 2 Date	Stage 3 Date	Stage 4 Date		Start Date	End Date	Result
2	570	3180	9073	1772	9/7/2022	10/7/2022	11/9/2022	12/5/2022		9/1/2022	9/30/2022	9047
3	1834	5458	5469	7875	3/12/2022	4/7/2022	5/7/2022	6/13/2022
4	4294	8165	8477	2701	7/30/2022	8/30/2022	9/21/2022	10/8/2022
5	3019	9472	2569	7433	4/14/2022	5/16/2022	5/30/2022	7/5/2022
6	9662	5729	7187	7461	4/19/2022	5/10/2022	6/11/2022	7/14/2022
Sheet1

Cell Formulas
Range	Formula
L2	L2	=SUMPRODUCT(--($E$2:$H$6>=J2),--($E$2:$H$6<=K2),$A$2:$D$6)

 

[Sufiyan97]

Hi @Domenic

Will it cause any error if we don't put "--"?

Book3
	A	B	C	D	E	F	G	H	I
1	Stage 1	Stage 2	Stage 3	Stage 4	Stage 1 Date	Stage 2 Date	Stage 3 Date	Stage 4 Date
2	570	3180	9073	1772	9/7/2022	10/7/2022	11/9/2022	12/5/2022
3	1834	5458	5469	7875	3/12/2022	4/7/2022	5/7/2022	6/13/2022
4	4294	8165	8477	2701	7/30/2022	8/30/2022	9/21/2022	10/8/2022
5	3019	9472	2569	7433	4/14/2022	5/16/2022	5/30/2022	7/5/2022
6	9662	5729	7187	7461	4/19/2022	5/10/2022	6/11/2022	7/14/2022
7
8
9	Start Date	End Date
10	9/1/2022	9/30/2022
11	Total	9047
12
13
14
Sheet2

Cell Formulas
Range	Formula
B11	B11	=SUMPRODUCT(A2:D6*(E2:H6>=A10)*(E2:H6<=B10))

 

[karldugan]

Is this what you mean?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Stage 1	Stage 2	Stage 3	Stage 4	Stage 1 Date	Stage 2 Date	Stage 3 Date	Stage 4 Date		Start Date	End Date	Result
2	570	3180	9073	1772	9/7/2022	10/7/2022	11/9/2022	12/5/2022		9/1/2022	9/30/2022	9047
3	1834	5458	5469	7875	3/12/2022	4/7/2022	5/7/2022	6/13/2022
4	4294	8165	8477	2701	7/30/2022	8/30/2022	9/21/2022	10/8/2022
5	3019	9472	2569	7433	4/14/2022	5/16/2022	5/30/2022	7/5/2022
6	9662	5729	7187	7461	4/19/2022	5/10/2022	6/11/2022	7/14/2022
Sheet1

Cell Formulas
Range	Formula
L2	L2	=SUMPRODUCT(--($E$2:$H$6>=J2),--($E$2:$H$6<=K2),$A$2:$D$6)

That's the one!

Why do you have to put two minuses in front of the criteria though? Could you explain how it works as I never really understand sumproduct thank you for the help too though!

 

[kvsrinivasamurthy]

Data A2:H6
Formula

=SUMPRODUCT(($E$2:$H$6>=A9)*($E$2:$H$6<=B9)*$A$2:$D$6)

Stage 1	Stage 2	Stage 3	Stage 4	Stage 1 Date	Stage 2 Date	Stage 3 Date	Stage 4 Date
1	2.00	3.00	4.00	07-09-2022	07-10-2022	09-11-2022	05-12-2022
1	2.00	3.00	4.00	12-03-2022	07-04-2022	07-05-2022	13-06-2022
1	2.00	3.00	4.00	30-07-2022	30-08-2022	21-09-2022	08-10-2022
1	2.00	3.00	4.00	14-04-2022	16-05-2022	30-05-2022	05-07-2022
1	2.00	3.00	4.00	19-04-2022	10-05-2022	11-06-2022	14-07-2022
A9	B9
01-09-2022​	30-09-2022​	=SUMPRODUCT(($E$2:$H$6>=A9)*($E$2:$H$6<=B9)*$A$2:$D$6)

 

[Domenic]

@karldugan, @Sufiyan97

You'll notice that the formula . . .

=SUMPRODUCT(--($E$2:$H$6>=J2),--($E$2:$H$6<=K2),$A$2:$D$6)

. . . uses the comma syntax. And you'll notice that it contains three arguments...

--($E$2:$H$6>=J2)  ===> first argument
--($E$2:$H$6<=K2) ===> second argument
$A$2:$D$6  ===> third argument

($E$2:$H$6>=J2) returns an array of TRUE and FALSE. However, since the comma syntax requires numerical values, and not logical values, the double negative ( --- ) is used to coerce those values into their numerical equivalent, which would be 1 and 0, respectively. The same thing applies to --($E$2:$H$6<=K2).

So each element from the first argument is multiplied by its corresponding elements in the second and third argument. Then the values from the resulting array is summed.

You can see how the evaluation takes place by selecting the cell containing the formula and then clicking on Evaluate Formula (Ribbon >> Formulas tab >> Formula Auditing group).

With the following syntax...

=SUMPRODUCT(($E$2:$H$6>=J2)*($E$2:$H$6<=K2)*($A$2:$D$6))

...there's no need to coerce TRUE and FALSE values into their numerical equivalents. Those values are automatically converted to their numerical equivalent when multiplied.

Note, however, the comma syntax is more efficient.

Hope this helps!