Sumproduct question

[keresztesi]

Hi,

i have a following question.
What would be the correct formula to have the sum value?
I'd like to use sumproduct because I got the values in external excel files.

Variables:
From 02.okt
To 06.okt
only text4

1	A1	B1	C1	D1	E1	F1	G1	H1	I1	J1
2		01.oct	02.oct	03.oct	04.oct	05.oct	06.oct	07.oct	08.oct	09.oct
3	text1	991	983	377	839	994	1	821	734	25
4	text2	112	582	505	871	685	687	295	686	421
5	text3	220	299	442	488	677	879	745	93	92
6	text4	342	882	9	507	898	369	354	639	925
7	text5	770	386	981	548	326	170	916	129	621
8	text6	4	885	626	582	763	800	95	86	361
9	text7	673	66	467	775	405	972	869	317	333
10	text8	979	741	772	552	646	182	485	405	465
11	text9	473	782	335	928	548	918	627	105	804
12	text10	850	206	147	967	327	278	393	528	141
13	text11	958	383	229	268	230	85	415	632	842

<colgroup><col><col><col span="9"></colgroup><tbody>
</tbody>

Thx,
Zoli

 

[FormR]

Hi, assuming the values in row 1 are dates with special formatting and the values in column A are unique - you could try this:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1		01-Oct	02-Oct	03-Oct	04-Oct	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct
2	text1	991	983	377	839	994	1	821	734	25		2665
3	text2	112	582	505	871	685	687	295	686	421
4	text3	220	299	442	488	677	879	745	93	92
5	text4	342	882	9	507	898	369	354	639	925
6	text5	770	386	981	548	326	170	916	129	621
7	text6	4	885	626	582	763	800	95	86	361
8	text7	673	66	467	775	405	972	869	317	333
9	text8	979	741	772	552	646	182	485	405	465
10	text9	473	782	335	928	548	918	627	105	804
11	text10	850	206	147	967	327	278	393	528	141
12	text11	958	383	229	268	230	85	415	632	842
Sheet1

Cell Formulas
Range	Formula
L2	=SUMPRODUCT(INDEX($B$2:$J$12,MATCH("Text4",$A$2:$A$12,0),0),--($B$1:$J$1>=DATE(2017,10,2)),--($B$1:$J$1<=DATE(2017,10,6)))

 

[keresztesi]

Hi, assuming the values in row 1 are dates with special formatting and the values in column A are unique - you could try this:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1		01-Oct	02-Oct	03-Oct	04-Oct	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct
2	text1	991	983	377	839	994	1	821	734	25		2665
3	text2	112	582	505	871	685	687	295	686	421
4	text3	220	299	442	488	677	879	745	93	92
5	text4	342	882	9	507	898	369	354	639	925
6	text5	770	386	981	548	326	170	916	129	621
7	text6	4	885	626	582	763	800	95	86	361
8	text7	673	66	467	775	405	972	869	317	333
9	text8	979	741	772	552	646	182	485	405	465
10	text9	473	782	335	928	548	918	627	105	804
11	text10	850	206	147	967	327	278	393	528	141
12	text11	958	383	229	268	230	85	415	632	842
Sheet1

Cell Formulas
Range	Formula
L2	=SUMPRODUCT(INDEX($B$2:$J$12,MATCH("Text4",$A$2:$A$12,0),0),--($B$1:$J$1>=DATE(2017,10,2)),--($B$1:$J$1<=DATE(2017,10,6)))

It gives me "0" back.

 

[Aladin Akyurek]

It seems Text4 occur more than once in column A.

If so, control+shift+enter, not just enter:

=SUM(IF($A$2:$A$12="Text4",IF($B$1:$J$1>=DATE(2017,10,2),IF($B$1:$J$1<=DATE(2017,10,6),$B$2:$J$12))))

Note that B1:J1 must house true dates, where, e.g., ISNUMBER(B1) yields TRUE.

 

[keresztesi]

It seems Text4 occur more than once in column A.

If so, control+shift+enter, not just enter:

=SUM(IF($A$2:$A$12="Text4",IF($B$1:$J$1>=DATE(2017,10,2),IF($B$1:$J$1<=DATE(2017,10,6),$B$2:$J$12))))

Note that B1:J1 must house true dates, where, e.g., ISNUMBER(B1) yields TRUE.

Great, thank you, it works fine!