Sumif not working

syed_mushraf

Active Member
Joined
Oct 13, 2002
Messages
259
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Hi,

How about...?

=SUM(OFFSET(A1,MATCH(I3,A1:A8,0),0,1,7))
Book2
ABCDEFGHI
1Week-1
21234567
3Week-2Week-2
489101112131477
5Week-3
615161718192021
7Week-4
822232425262728
9
Sheet1
 

syed_mushraf

Active Member
Joined
Oct 13, 2002
Messages
259
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Do you mean like this?

=SUM(OFFSET(A1,MATCH(I6,A1:A12,0)+1,0,1,7))
Book2
ABCDEFGHI
1Week-1
2
31234567
4Week-2
5
6891011121314Week-2
7Week-377
8
915161718192021
10Week-4
11
1222232425262728
Sheet1
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
I still recommend you structure your data differently, as opposed to using merged cells. They are the devil. ( ;) )
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
firefytr said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,731
Messages
5,573,889
Members
412,555
Latest member
mark84
Top