# Sumif not working

#### syed_mushraf

##### Active Member
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### RAM

##### Well-known Member
Why Sumif? What's the criteria?

RAM

#### Zack Barresse

##### MrExcel MVP
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

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

#### fairwinds

##### MrExcel MVP

Hi,

=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
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

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
I still recommend you structure your data differently, as opposed to using merged cells. They are the devil. ( )

#### fairwinds

##### MrExcel MVP
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.

Replies
4
Views
501
Replies
1
Views
296
Replies
1
Views
353
Replies
12
Views
447
Replies
1
Views
200

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,853
Messages
5,833,991
Members
430,250
Latest member
Reggie Mcqueen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back