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

Weeks	Week 17	Week 18	Week 19
Airline	(need this total from the second example table)	(need this total from the second example Table)	(need this total from the second example Table)
Tools	(need this total from the second example Table)	(need this total from the second example Table)	(need this total from the second example Table)
Car	(need this total from the second example Table)	(need this total from the second example Table)	(need this total from the second example Table)
Payroll	(need this total from the second example Table)	(need this total from the second example Table)	(need this total from the second example Table)

<tbody>
</tbody>

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.

Week Number	Charge Category (types)	Charge Amount	Save Location	Project Name
Week 17	Tools	800.00	(sharepoint url)	Name of the project
Week 17	Approved Incidentals	300.00	(sharepoint url)	Name of the project.
Week 18	Payroll	50000.00	SharePoint	Project
Week 18	Tools	500.00	SharePoint	Project

<tbody>
</tbody>

 

[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.