# SUMIF and Multiples

#### creinmi

##### New Member
I apologize for posting this twice - should have just posted the whole question at once. In any this is my problem:

On Sheet 1 I have

Column A - the first year that an expense is incurred (a number betwen 1 and 30)
Column B - a year denoting how many years until it will be incurred again (a number between 1 and 30)
Column C - The expense amount

On Sheet 2 I have

Row 1 - The years (numbered 1 through 30)

In row 2 I want to put a formula that will show the total expenses incurred in that year

As an example

A B C
1 5 \$100
2 2 \$60
8 8 \$50

So in the cell underneath year 6 I want it to return \$160 (\$100 + \$60) and in the cell beneath year 8 I want it to return \$110 (\$60 + \$50)

There could be hundreds of data entries on Sheet 1 so I dont want to have to do an IF statement for every entry. Any help would be MUCH appreciated as I am STUMPED!

Thanks -

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### jmckeone

##### Well-known Member
a few questions for you:

Is column B on the first sheet a one time event or a recurring event?

1 5 \$100
is that year 1 & 6
or
years 1, 6, 11, 16, 21, 26 (all those years falling under 30)?
Book1
ABCDE
11stYearNextYearExpenseYearofnextexpense
215\$1006
322\$604
488\$5016
Sheet1

Using an array command will do the trick nicely.
Book1
ABCDEF
1123456
2\$100.00\$60.00\$75.00\$185.00\$-\$100.00
Sheet2

#### pgc01

##### MrExcel MVP
Hi creinmi

With the layout in the example, write in Sheet2!A2

Code:
``=SUMPRODUCT(--(A\$1>=Sheet1!\$A\$2:\$A\$4),--(MOD(A\$1-Sheet1!\$A\$2:\$A\$4,Sheet1!\$B\$2:\$B\$4)=0),Sheet1!\$C\$2:\$C\$4)``
Copy across

Hope this helps
PGC

EDIT: Added a test to the formula
Book1.xls
ABCD
1ABC
215\$100
322\$60
488\$50
5
6
Sheet1
Book1.xls
ABCDEFGHIJ
112345678910
21006006001600110060
3
4
Sheet2

Hi again creinmi

nm

#### creinmi

##### New Member
Shoot (should have included this before) - some of the events don't reoccur (0 value in column B) which returns the #DIV/0! - any way to adjust for that?

#### pgc01

##### MrExcel MVP
Hi creinmi

I changed the second condition of the SUMPRODUCT to test if the value in column B is zero.

I added 2 lines with no recurrence to my sample data.

Remark: you'll have to enter the formula as an array formula.

In Sheet2!A2:

Code:
``=SUMPRODUCT(--(A\$1>=Sheet1!\$A\$2:\$A\$6),IF(Sheet1!\$B\$2:\$B\$6=0,--(A\$1=Sheet1!\$A\$2:\$A\$6),--(MOD(A\$1-Sheet1!\$A\$2:\$A\$6,Sheet1!\$B\$2:\$B\$6)=0)),Sheet1!\$C\$2:\$C\$6)``
This in an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy across.

Hope it helps
PGC

P. S. Sorry about my ignorance but what does nm mean?
Book1
ABCD
1ABC
215\$100
322\$60
488\$50
530\$200
660\$150
7
Sheet1
Book1
ABCDEFGHIJ
112345678910
2\$100\$60\$200\$60\$0\$310\$0\$110\$0\$60
3
Sheet2

Replies
2
Views
94
Replies
0
Views
347
Replies
3
Views
281
Replies
2
Views
104
Replies
1
Views
159

1,136,878
Messages
5,678,320
Members
419,755
Latest member
eshnider21

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