Sumproduct question

keresztesi

Board Regular
Joined
Aug 14, 2017
Messages
64
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
ABCDEFGHIJKL
101-Oct02-Oct03-Oct04-Oct05-Oct06-Oct07-Oct08-Oct09-Oct
2text19919833778399941821734252665
3text2112582505871685687295686421
4text32202994424886778797459392
5text43428829507898369354639925
6text5770386981548326170916129621
7text648856265827638009586361
8text767366467775405972869317333
9text8979741772552646182485405465
10text9473782335928548918627105804
11text10850206147967327278393528141
12text1195838322926823085415632842
Sheet1
Cell Formulas
RangeFormula
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)))
 
Upvote 0
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
ABCDEFGHIJKL
101-Oct02-Oct03-Oct04-Oct05-Oct06-Oct07-Oct08-Oct09-Oct
2text19919833778399941821734252665
3text2112582505871685687295686421
4text32202994424886778797459392
5text43428829507898369354639925
6text5770386981548326170916129621
7text648856265827638009586361
8text767366467775405972869317333
9text8979741772552646182485405465
10text9473782335928548918627105804
11text10850206147967327278393528141
12text1195838322926823085415632842
Sheet1
Cell Formulas
RangeFormula
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. :(
 
Upvote 0
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.
 
Upvote 0
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! :-)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top