Returning a value between two dates

[sheepshagarmy]

Looking to set up a table as per below

The values I would like returned underneath the "no. of fruit" I would like to pull from another table in a separate tab, which contains all weeks (1-52). In this table there are values for each of the type of fruit (has nothing to do with fruit really!! ) for each of the individual weeks.

I would like the total for all the weeks specified in the top part. I.e. if I've typed, like below 31-52 I would like all the summed values for all of the weeks inclusive of 31 and 52.

Any help would be appreciated

Thanks

[TABLE="width: 500"]
<tbody>[TR]
[TD]Week from:[/TD]
[TD]31[/TD]
[TD]Week To:[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No. of Apples[/TD]
[TD]No. of Pears[/TD]
[TD]No. of Lemons[/TD]
[TD]No. of Oranges[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

You should have described the layout of the other table as well.
Post part of the other table as well, not just the summary table

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

Attachments

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[sheepshagarmy]

I'm on a protected work network and can't download anything.

Don't know if this will help but below is the format of the "other table"

There are a lot more columns, its just a small example.

So using this example I would want to put in (e.g.) 33-34 and it would give me 6 Pears, 30 Apples, 1 Lemon and 4 Oranges on the other table....

Make sense?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Week No.[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Week commencing[/TD]
[TD]07-Aug[/TD]
[TD]14-Aug[/TD]
[TD]21-Aug[/TD]
[TD]2-Aug[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]25[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Week No.[/TD]
[TD]

32​

[/TD]
[TD]

33​

[/TD]
[TD]

34​

[/TD]
[TD]

35​

[/TD]
[TD][/TD]
[TD]Week from:[/TD]
[TD]

33​

[/TD]
[TD]Week To:[/TD]
[TD]

34​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Week commencing[/TD]
[TD]

7-Aug​

[/TD]
[TD]

14-Aug​

[/TD]
[TD]

21-Aug​

[/TD]
[TD]

2-Aug​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Pears[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

67​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Apples[/TD]
[TD]

25​

[/TD]
[TD]

18​

[/TD]
[TD]

12​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lemons[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Lemons[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Oranges[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


i2=SUMPRODUCT(($A$3:$A$6=$H2)*($B$1:$E$1>=$H$1)*($B$1:$E$1<=$J$1)*($B$3:$E$6)) copy down