I think I need SUMPRODUCT but I am not convinced

[stugi]

Hi,

I am trying to sum a list of data using mutiple criteria and I cannot figure out the best way. An example of the data is as follows

Col A Col B Col C Col D
Year Period Type Value
2012 9 EPC 10500
2012 11 EPC 26500
2012 12 ENB 750
2013 2 OWN 8236
2013 9 PLN 1200
2013 12 EPC 30000

I want to sum Col D by the various Types in Col C, however I need to do it upto the current period -1. So if for example we are in Period 12 2013, then sum all the sum of spend for Type EPC upto Year 2013 Period 11.

I cannot figure out how to do this without excluding the Year 2012 Period 12 values as well as Year 2013 Period 12.

any suggestions greatly received

 

[ashish koul]

have u tired using sumifs on it . can you share the sample file with some examples

 

[Dave Patton]

Excel Workbook
	A	B	C	D
1	Criteria
2	2013	12	EPC	0.00
3	2013	12	PLN	1,200.00
4
5	Year	Period	Type	Value
6	2012	9	EPC	10,500.00
7	2012	11	EPC	26,500.00
8	2012	12	ENB	750.00
9	2013	2	OWN	8,236.00
10	2013	9	PLN	1,200.00
11	2013	12	EPC	30,000.00
9a

Excel 2003

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--($A$6:$A$11=A2),--($B$6:$B$11),--($C$6:$C$11=C2),($D$6:$D$11))
D3	=SUMPRODUCT(--($A$6:$A$11=A3),--($B$6:$B$11),--($C$6:$C$11=C3),($D$6:$D$11))

What version of Excel are you using?

2007 and > try Sumifs

 

[JASONRUBALCAVA]

THIS IS WHAT I CAME UP WITH.. END RESULT

	A	B	C	D	E	F	G
1	year	period	type	value		type	value
2	2012	9	epc	10500		epc	67000
3	2012	11	epc	26500
4	2012	12	enb	750
5	2013	2	own	8236
6	2013	9	pln	1200
7	2013	12	epc	30000

<colgroup><col style="width:48pt" width="64"> <col style="width:48pt" span="7" width="64"> </colgroup><tbody>
</tbody>

\
IN CELL F1 PUT "TYPE" AND G1 "VALUE" IN CELL F2 INSERT DATA VALIDATION DROP DOWN AND HIGH LIGHT COLUMN C.. IN CELL G2 FORMULA IS =SUMIF(D3:D8,G3,E3:E8) WHEN YOU CHANGE THE DROP DOWN TO ANY OTHER TYPES... IT WILL SUM THAT VALUE..

 

[stugi]

thanks guys, I am on 2010 and I was using Sumifs when I came a cropper.

This is my current formula

=SUMIFS('GL Data'!G:G,'GL Data'!A:A,"<="&Ref!$C$2,'GL Data'!B:B,"<"&Ref!$C$3,'GL Data'!E:E,'Cost Report'!C8)/1000


So "GL Data" is the tab with the columns of data I wish to interrogate (see smaple below). "Ref" is the tab where I get the 'Year' & 'Period' from, "Cost Report" is the tab I am doing the calculation on and Column C on "Cost Report" is where the 'Type' reference is.

Sample data (sorry do not know how to upload the samples as you have done)

A B C
Year Period BD Offline Code Total
2012 12 OWN 29,967.82
2012 12 LND 4,331,866.00
2013 11 EPC 980,452.26
2013 11 EPC 1,960,904.52
2013 11 OWN 45,714.29
2013 11 OWN 1,593.75
2013 11 OWN 4,023.07
2013 12 OWN 45,714.29
2013 12 OWN 45,714.29
2013 12 EPC 1,470,678.39
2013 12 EPC 1,960,904.52
2013 12 EPC 1,960,904.52
2013 12 EPC 2,078,339.90
2013 12 EPC 1,039,169.95
2013 12 EPC 5,612,220.83
2013 12 EPC 11,224,441.65
2013 12 EPC -11,224,441.65
2013 12 EPC -5,612,220.82
2013 12 EPC 735,339.20
2013 12 OWN 2,985.61
2013 12 EPC 11,443,174.36
2013 12 EPC 5,721,587.18
2013 12 EPC 735,339.20
2013 12 EPC 64,287.50
2013 12 EPC -393,194.82
2013 12 LND 2,500,000.00

The problem is I need the combination of Year 2013 and Period 12 to be excluded, however my currnet formula excludes all Period 12 entries because I cannot specify Year 2013 AND Period 12, I cannot make the link between these two criteria in the formula.

Sorry If this is not clear

 

[Dave Patton]

Did you try the suggestion in post #3?

 

[JASONRUBALCAVA]

if send me ur email . i can send you what I think will work for you. it does what you want.. i just used sumifs with 2 criteria and data validation.. i wll look like this

 

[JASONRUBALCAVA]

here is what i made.. your should be able to download it


https://www.dropbox.com/s/6zzwgcghdhxtqzg/excel sheet 1.xlsx

you should be able to download it here..

 

[stugi]

I did not as the last comment said try Sumifs, so outlined what i had done with Sumifs

 

[Dave Patton]

A Sumifs to try

=SUMIFS(D6:D11,A6:A11,A3,C6:C11,C3,B6:B11,"<"&B3)