SUMIF and Multiples

creinmi

New Member
Joined
Jun 19, 2006
Messages
16
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
Joined
Jun 3, 2006
Messages
550
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
Joined
Apr 25, 2006
Messages
19,884
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again creinmi
Please notice that I added a test to the formula.
 

creinmi

New Member
Joined
Jun 19, 2006
Messages
16
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
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top