How do I get the sum of data from multiple sheets based on a certain criteria?

[sajanvgs]

How do I get the sum of data from multiple sheets based on a certain criteria to a summary sheet, either using formula or vba?

For example
Currently I have two worksheets but this can go up to 13 or 15 worksheets, I want the sum to be shown on summary sheet (sumsheet) for each week separately and the row count for each worksheet will differ.

Worksheet (Data1) has
[TABLE="width: 348"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Name
[/TD]
[TD]Qty
[/TD]
[TD]Week
[/TD]
[/TR]
[TR]
[TD]2-Jan-15
[/TD]
[TD]Tim
[/TD]
[TD]25
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2-Jan-15
[/TD]
[TD]Tom
[/TD]
[TD]54
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3-Jan-15
[/TD]
[TD]James
[/TD]
[TD]34
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4-Jan-15
[/TD]
[TD]Jack
[/TD]
[TD]77
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]5-Jan-15
[/TD]
[TD]Eric
[/TD]
[TD]19
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet (Data2) has
[TABLE="width: 354"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Name
[/TD]
[TD]Qty
[/TD]
[TD]Week
[/TD]
[/TR]
[TR]
[TD]5-Jan-15
[/TD]
[TD]Tim
[/TD]
[TD]77
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]6-Jan-15
[/TD]
[TD]Tom
[/TD]
[TD]54
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]21-Jan-15
[/TD]
[TD]Patrick
[/TD]
[TD]44
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]7-Jan-15
[/TD]
[TD]James
[/TD]
[TD]34
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]8-Jan-15
[/TD]
[TD]Jack
[/TD]
[TD]25
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]14-Jan-15
[/TD]
[TD]Tim
[/TD]
[TD]12
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]5-Jan-15
[/TD]
[TD]Eric
[/TD]
[TD]36
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet (sumsheet) were the “1”, “2” …. Stands for the week

[TABLE="width: 236"]
<tbody>[TR]
[TD]1
[/TD]
[TD]Total Qty
[/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Jack
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Eric
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Patric
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Total Qty
[/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Jack
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Eric
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Patric
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

Try entering all your tab names (like data1;data2) in a range on sheet and define a name for the range, eg: Sheetlist.
See my example below.

Book1
	A	B	C	D
1	1	Total Qty		Data1
2	Tim	25		Data2
3	Tom	54
4	James	34
5	Jack	0
6	Eric	0
7	Patrick	0
8
9	2	Total Qty
10	Tim	77
11	Tom	54
12	James	34
13	Jack	102
14	Eric	55
15	Patrick	0
16
17	7	Total Qty
18	Tim	0
19	Tom	0
20	James	0
21	Jack	0
22	Eric	0
23	Patrick	44
Sheet3

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$1,INDIRECT("'"&Sheetlist&"'!b:b"),A2))
B3	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$1,INDIRECT("'"&Sheetlist&"'!b:b"),A3))
B4	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$1,INDIRECT("'"&Sheetlist&"'!b:b"),A4))
B5	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$1,INDIRECT("'"&Sheetlist&"'!b:b"),A5))
B6	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$1,INDIRECT("'"&Sheetlist&"'!b:b"),A6))
B7	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$1,INDIRECT("'"&Sheetlist&"'!b:b"),A7))
B10	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$9,INDIRECT("'"&Sheetlist&"'!b:b"),A10))
B11	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$9,INDIRECT("'"&Sheetlist&"'!b:b"),A11))
B12	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$9,INDIRECT("'"&Sheetlist&"'!b:b"),A12))
B13	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$9,INDIRECT("'"&Sheetlist&"'!b:b"),A13))
B14	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$9,INDIRECT("'"&Sheetlist&"'!b:b"),A14))
B15	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$9,INDIRECT("'"&Sheetlist&"'!b:b"),A15))
B18	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$17,INDIRECT("'"&Sheetlist&"'!b:b"),A18))
B19	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$17,INDIRECT("'"&Sheetlist&"'!b:b"),A19))
B20	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$17,INDIRECT("'"&Sheetlist&"'!b:b"),A20))
B21	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$17,INDIRECT("'"&Sheetlist&"'!b:b"),A21))
B22	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$17,INDIRECT("'"&Sheetlist&"'!b:b"),A22))
B23	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist&"'!c:c"),INDIRECT("'"&Sheetlist&"'!d:d"),$A$17,INDIRECT("'"&Sheetlist&"'!b:b"),A23))

Named Ranges
Name	Refers To	Cells
Sheetlist	=Sheet3!$D$1:$D$2

 

[sajanvgs]

This is possible if the customer list is small but what if the customer list is very huge, and keeps on repeating in each sheet. I receive each data sheet in a CSV extension and import it separately to each worksheet, I believe including all the data from different vendor under one sheet will not be possible.

It would be easier to calculate the gross sales in each week from different worksheet based on the criteria week number for different customers.