# Create column totals by period

#### nickwetherall

##### New Member
sum test.xls
BCDEFGH
411112
5detailsPeriodWeek1Week2Week3Week4Week5
607/01/200714/01/200721/01/200728/01/200704/02/2007
7Renttwo5057575757
8Commissionone6043434343
9
10Renttwo455555
11Commissionone5543434343
Sheet1

Please can somebody help me with what is more than likely a simple problem.

On the above table I would like to be able to create a formula that will add together the figures for all columns with a 1 and all rows with Rent as a detail giving a total of 281.

I have tried sumif and sumproduct formula's, but they only add together the first column. Is there a way of extending these to cover the other columns.

Thank you in advance for you help.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
{=SUM(IF((A4:A100="Rent")*(C1:IV1=1),(C4:M100)))}

nickwetherall,

This works:
=SUMPRODUCT((\$B\$4:\$B\$8="Rent")*(\$D\$4:\$H\$8)*(\$D\$1:\$H\$1=1))

Have a great day,
Stan

Replies
11
Views
442
Replies
4
Views
265
Replies
1
Views
545
Replies
1
Views
96
Replies
8
Views
239

1,221,186
Messages
6,158,414
Members
451,492
Latest member
ichinisan123

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