Sumproduct question

keresztesi · Oct 3, 2017

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

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

Thx,
Zoli

FormR · Oct 3, 2017

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 · Oct 4, 2017

FormR said:
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 · Oct 4, 2017

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 · Oct 4, 2017

Aladin Akyurek said:
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!

Sumproduct question

keresztesi

Board Regular

Excel Facts

FormR

MrExcel MVP

keresztesi

Board Regular

Aladin Akyurek

MrExcel MVP

keresztesi

Board Regular

Similar threads

Forum statistics

Share this page

Sumproduct question

keresztesi

Board Regular

Excel Facts

FormR

MrExcel MVP

keresztesi

Board Regular

Aladin Akyurek

MrExcel MVP

keresztesi

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock