Sumif not working

[syed_mushraf]

cell Sheet1!A1 to Sheet1!G1 is merged cell with heading "Week-1"
cell Sheet1!A2 to Sheet1!G2 have some data

Cell Sheet1!H1 to Sheet1!N1 is merged cell for heading "Week-2"
cell Sheet1!H2 to Sheet1!G2 have some data

----------------------------------------------------

I need a summary in the sheet!2 by using sumif function

Cell Sheet2!A1 is containing "Week-1"
in Shee2!A2 i need sum of Sheet1!A2:G2 using Sumif "Week-1"

Plz help how will be done

 

[RAM]

Why Sumif? What's the criteria?

RAM

 

[Zack Barresse]

Merged cells are hell to deal with. Is there any chance of you unmerging them? I ask before any solution is posted as it will make a difference of being easy or difficult, from now through the life of this spreadsheet.

 

[RAM]

Good point, of course, but please help me out, why Sumif? You already have the heading in both sheets and they're not going anywhere.

Why not just a sum formula in Shet2 A1, like =SUM(Sheet1!A2:G2)

 

[fairwinds]

Hi,

How about...?

=SUM(OFFSET(A1,MATCH(I3,A1:A8,0),0,1,7))

Book2
	A	B	C	D	E	F	G	H	I
1	Week-1
2	1	2	3	4	5	6	7
3	Week-2								Week-2
4	8	9	10	11	12	13	14		77
5	Week-3
6	15	16	17	18	19	20	21
7	Week-4
8	22	23	24	25	26	27	28
9
Sheet1

 

[syed_mushraf]

Hi fairwinds,
thankx for response .
it is working fine but if there is another row exits for Dates between week and data then what would be the function. We want to skip date filed while summing up the data of next row.

 

[fairwinds]

Do you mean like this?

=SUM(OFFSET(A1,MATCH(I6,A1:A12,0)+1,0,1,7))

Book2
	A	B	C	D	E	F	G	H	I
1	Week-1
2
3	1	2	3	4	5	6	7
4	Week-2
5
6	8	9	10	11	12	13	14		Week-2
7	Week-3								77
8
9	15	16	17	18	19	20	21
10	Week-4
11
12	22	23	24	25	26	27	28
Sheet1

 

[Zack Barresse]

I still recommend you structure your data differently, as opposed to using merged cells. They are the devil. ( )

 

[fairwinds]

They are the devil. ( )

I think it is a handy feature for making nice and appealing layouts. Making this is also an important and useful excel skill. As long as you know what you are doing, they don't mess up anything.

I would be more concerned about people using VBA code and advanced formulas given here, without actually understand what result they give and why.