SUMIF and Multiples

[creinmi]

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 -

 

[jmckeone]

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
	A	B	C	D	E
1	1stYear	NextYear	Expense		Yearofnextexpense
2	1	5	$100		6
3	2	2	$60		4
4	8	8	$50		16
Sheet1

Using an array command will do the trick nicely.

Book1
	A	B	C	D	E	F
1	1	2	3	4	5	6
2	$100.00	$60.00	$75.00	$185.00	$-	$100.00
Sheet2

 

[pgc01]

Hi creinmi

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

=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
	A	B	C	D
1	A	B	C
2	1	5	$100
3	2	2	$60
4	8	8	$50
5
6
Sheet1

Book1.xls
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	8	9	10
2	100	60	0	60	0	160	0	110	0	60
3
4
Sheet2

 

[pgc01]

Hi again creinmi
Please notice that I added a test to the formula.

 

[creinmi]

nm

 

[creinmi]

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]

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:

=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
	A	B	C	D
1	A	B	C
2	1	5	$100
3	2	2	$60
4	8	8	$50
5	3	0	$200
6	6	0	$150
7
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	8	9	10
2	$100	$60	$200	$60	$0	$310	$0	$110	$0	$60
3
Sheet2