Is the sumif the best option?

[islic1411]

Good Morning MrExcel!

I am trying to get the syntax correct but I am not 100% sure the SumIF is the right command for what I am after or if it should be some very long if statement.
I would greatly appreciate any insight you have:

I think can do this with pivot charts to get the sum but I am trying to pull over totals based on category (project charge type) and week #. I am trying to build it into a standard accounting sheet type format and wasn't real sure if that was the right path to take either.

I am pulling the data below, I have my accounting sheet broken out into the revenue along the top and then a line to reduce the revenue by that weeks revenue. I have 14 different project charge types

[TABLE="width: 500"]
<tbody>[TR]
[TD]Weeks[/TD]
[TD]Week 17[/TD]
[TD]Week 18[/TD]
[TD]Week 19[/TD]
[/TR]
[TR]
[TD]Airline[/TD]
[TD](need this total from the second example table)[/TD]
[TD](need this total from the second example Table)[/TD]
[TD](need this total from the second example Table)[/TD]
[/TR]
[TR]
[TD]Tools[/TD]
[TD](need this total from the second example Table)[/TD]
[TD](need this total from the second example Table)[/TD]
[TD](need this total from the second example Table)[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD](need this total from the second example Table)[/TD]
[TD](need this total from the second example Table)[/TD]
[TD](need this total from the second example Table)[/TD]
[/TR]
[TR]
[TD]Payroll[/TD]
[TD](need this total from the second example Table)[/TD]
[TD](need this total from the second example Table)[/TD]
[TD](need this total from the second example Table)[/TD]
[/TR]
</tbody>[/TABLE]


Example table #2 (this is a data connection coming from SharePoint and is pulled into a table) There will be about 15 weeks worth of charges, I am guessing about 250 lines or so once the project is nearing the end.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Week Number[/TD]
[TD]Charge Category (types)[/TD]
[TD]Charge Amount[/TD]
[TD]Save Location[/TD]
[TD]Project Name[/TD]
[/TR]
[TR]
[TD]Week 17[/TD]
[TD]Tools[/TD]
[TD]800.00[/TD]
[TD](sharepoint url)[/TD]
[TD]Name of the project[/TD]
[/TR]
[TR]
[TD]Week 17[/TD]
[TD]Approved Incidentals[/TD]
[TD]300.00[/TD]
[TD](sharepoint url)
[/TD]
[TD]Name of the project.[/TD]
[/TR]
[TR]
[TD]Week 18[/TD]
[TD]Payroll[/TD]
[TD]50000.00[/TD]
[TD]SharePoint[/TD]
[TD]Project[/TD]
[/TR]
[TR]
[TD]Week 18[/TD]
[TD]Tools[/TD]
[TD]500.00[/TD]
[TD]SharePoint[/TD]
[TD]Project[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Looks like a SUMIFS() will do what you want, for example:

Excel 2013
	A	B	C	D	E	F	G	H	I	J
1	Week Number	Charge Category (types)	Charge Amount	Save Location	Project Name		Weeks	Week 17	Week 18	Week 19
2	Week 17	Tools	800	(sharepoint url)	Name of the project		Airline	0	0	0
3	Week 17	Approved Incidentals	300	(sharepoint url)	Name of the project.		Tools	800	500	0
4	Week 18	Payroll	50000	SharePoint	Project		Car	0	0	0
5	Week 18	Tools	500	SharePoint	Project		Payroll	0	50000	0
Sheet1

Cell Formulas
Range	Formula
H2	=SUMIFS($C:$C,$B:$B,$G2,$A:$A,H$1)

 

[CodeNinja]

Assuming you have the example table #2 in A1:D5,

using table nomenclature:
=SUMPRODUCT((Table1[Charge Category (types)]=B$1)*(Table1[Week Number]=$A3)*Table1[Charge Amount])

using standard nomenclature:
=SUMPRODUCT(($B$14:$B$17=$A2)*($A$14:$A$17=B$1)*$C$14:$C$17)

Good luck,

CN.