Sumifs

[ErickaTW]

[TABLE="class: grid, width: 15, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]WW12
[/TD]
[TD]WW13
[/TD]
[TD]WW14
[/TD]
[TD]WW15
[/TD]
[TD]WW16
[/TD]
[TD]WW17
[/TD]
[TD]WW18
[/TD]
[TD]WW19
[/TD]
[/TR]
[TR]
[TD]Model A
[/TD]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]5
[/TD]
[TD]20
[/TD]
[TD]15
[/TD]
[TD]50
[/TD]
[TD]10
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Model B
[/TD]
[TD]20
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]20
[/TD]
[TD]15
[/TD]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Part 1
[/TD]
[TD]30
[/TD]
[TD]20
[/TD]
[TD]10
[/TD]
[TD]40
[/TD]
[TD]30
[/TD]
[TD]60
[/TD]
[TD]25
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Model C
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Model D
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Part 2
[/TD]
[TD]10
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]11
[/TD]
[TD]6
[/TD]
[TD]6
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Summary
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]WW12
[/TD]
[TD]WW13
[/TD]
[TD]WW14
[/TD]
[TD]WW15
[/TD]
[TD]WW16
[/TD]
[TD]WW17
[/TD]
[TD]WW18
[/TD]
[TD]WW19
[/TD]
[/TR]
[TR]
[TD]Part1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]







Hello, I am having hard time trying to use SUMIFS I have a table with dates on rows and models on columns with data per date and per part number and I am trying to bring the data to the summary table for instance on WW12 for part 1 the total number would be 100, but I am not sure how to use SUMIFS or even if it is the correct formula to use.

 

[DSCfromCFA]

G'day Ericka,

Unless I am missing something (quite possible), in the example given you don't need to do a new calculation in the summary table as the Part 1 and 2 summary data is already calculated in the main table.

Simply make the summary cell = the corresponding cell in the main table. If the top left cell is A1, then the Part 1 total for WW12 is cell B4 and the part 1 summary for WW12 is cell B11 which only needs =B4 to dynamically reflect the main table data. Part 2 summary data in cell B12 would have =B7

Cheers

shane

 

[ErickaTW]

Thank you for the replay, but is just an example of a bigger file that I have to update in a regular basis,

Actually the summary table is in a different tab
And the 1st table with the data is going be updated every week, I want to be able just to copy the new data on the raw data and have my summary updated as needed.

I need a sum with two criteria's a) based on product and based on ww (work week)


Any help would be greatly appreciate it

 

[seanshurbet1]

try - I do not know your criteria so this is a guess - SUMIFS(SH1!$A:$A(qty t sum),SH1!$B:$B (wwto look at),"="&SH2!A$1(ww to match),SH1!$C:$C(item),SH2!$A2(item matching))

 

[seanshurbet1]

**=SUMIFS(H4:H9,$G$4:$G$9,"="&$G$13) this what you want

 

[DSCfromCFA]

The different tab makes little difference to excel, it could be in a different workbook and all that changes is a bit of extra information to tell the summary tab cell where to look for the data.

For example, if your data is in a tab called "Raw_Data" and the Summary in a tab called "Summary" and the cell addresses are as I gave them above, all that would change is that the =B4 would now be =Raw_Data!B4 and all that you need to type is the = sign and simply change tabs and click in the target cell then Enter.

I get the idea that you are simply overwriting the existing data in the Model cells for each Part and for each work week, so if the structure is as you have given above, then there is still no need for SUMIFS - a simple SUM of the rows above the Part 1 and Part 2 will do the job and will update automatically into the Summary tab whenever chaged based on the ='s formula above.

Cheers

shane

 

[DanteAmor]

How About:

Book1
	A	B	C	D	E	F	G	H	I
1		WW12	WW13	WW14	WW15	WW16	WW17	WW18	WW19
2	Model A	10	15	5	20	15	50	10	5
3	Model B	20	5	5	20	15	10	15	5
4	Part 1	30	20	10	40	30	60	25	10
5	Model C	5	1	2	1	10	5	5	5
6	Model D	5	1	1	1	1	1	1	1
7	Part 2	10	2	3	2	11	6	6	6
Sheet Data

Book1
	A	B	C	D	E	F	G	H	I
1		WW12	WW13	WW14	WW15	WW16	WW17	WW18	WW19
2	Part 1	30	20	10	40	30	60	25	10
3	Part 2	10	2	3	2	11	6	6	6
sheet Summary

Cell Formulas
Range	Formula
B2	=INDEX(Data!$A$1:$I$7,MATCH($A2,Data!$A$1:$A$7,0),MATCH(B$1,Data!$A$1:$I$1,0))

In the summary sheet, copy the formula to the right and then down.