using SUMIF for date ranges (with blanks)

[shemnel]

Hello,

I have some data that I want to add up cumulatively but it has a condition on its date of operation in other columns

for instance;

A	B	C	D
1	1234	1990
2	673	1990	1997
3	2134	1991
4	7878	1992	1996
5	0	1992
6	567	1994	1999
7	0	1996
8	43	1998
9	346	1998
10	0	1999

<colgroup><col span="4"></colgroup><tbody>
</tbody>

Column A is facility, B is amount produced, C is year opened and D is year shut (blank if not shut)

I want to produce a result that will allow me to cumulatively add up production year by year, 1990, then 1991 in the row below, 1992 below that etc, all beginning from 1990.

I can do that bit, but I am running into problems as when I get to say 1998, the sites that shut in 1996 can only be included up to 1996, they have to stop being counted in 1997 and 1998. Blank cells are also causing a problem in SUMIF (not sure 0 is acceptable in the final presentation)

thanks for your help

S

 

[Andrew Poulsom]

Welcome to MrExcel.

Is this what you want (formula in E1 copied down)?

Excel 2010

	A	B	C	D	E
1	1	1234	1990		1234
2	2	673	1990	1997	1907
3	3	2134	1991		4041
4	4	7878	1992	1996	11919
5	5	0	1992		11919
6	6	567	1994	1999	12486
7	7	0	1996		12486
8	8	43	1998		3978
9	9	346	1998		4324
10	10	0	1999		4324

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula E1 =SUMPRODUCT(--(C$1:C1<=C1),(ISBLANK(D$1:D1)+(D$1:D1>=C1)),B$1:B1) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[shemnel]

thanks very much, that is getting very close.

I think really I need a separate table below, where there is a cumulative total for every year, i.e. 1990 then below that 1991, and then 1992, then 1993 etc etc to 2000

what you have is pretty darn close though

 

[Andrew Poulsom]

Like this?

Excel 2010
	A	B	C	D
1	1	1234	1990
2	2	673	1990	1997
3	3	2134	1991
4	4	7878	1992	1996
5	5	0	1992
6	6	567	1994	1999
7	7	0	1996
8	8	43	1998
9	9	346	1998
10	10	0	1999
11
12	1990	1907
13	1991	4041
14	1992	11919
15	1993	11919
16	1994	12486
17	1995	12486
18	1996	12486
19	1997	4608
20	1998	4324
21	1999	4324
Sheet1

Cell Formulas
Range	Formula
B12	=SUMPRODUCT(--(C$1:C$10<=A12),(ISBLANK(D$1:D$10)+(D$1:D$10>=A12)),B$1:B$10)

 

[shemnel]

that's it i reckon, thanks for all your help