Summing by Column and Date

[nattiej101]

Hello!  I am trying to do a mass sum of different filters, but not sure how to go about it.

For example with the data attached, I need to sum all of the TMMC North information by month (April, May, June....) and then also TMMC West, TMMC South, ect for each month.  The Assembly, Weld, and Paint information all need to be summed into one.  

So If the cell is TMMC North, then sum all of the April data.

Sheet 2 has the information that needs to be summed, and Sheet 3 is the format I need the data in.

Thanks in advance!

[#2]

@nattiej101 

=SUMPRODUCT(($A2=Sheet2!$B$3:$B$20)*(Sheet3!$B2=Sheet2!$C$3:$C$20)*(MONTH(Sheet3!C$1)=MONTH(Sheet2!$D$21:$UG$21))*Sheet2!$D$3:$UG$20)

You can try this formula which returns the expected total of 19.930,03.

[#3]

@OliverScheurich I put that equation into my file and I got an error - what did I do wrong?

[#4]

I didn't open his file but it appears he added a line 21 the creates a proper date for every column.
You can try this version:
=SUMPRODUCT(($A2=Sheet2!$B$3:$B$20)*(Sheet3!$B2=Sheet2!$C$3:$C$20)*((MONTH(DATEVALUE(Sheet3!C$1 &"1, 2000"))=MONTH(Sheet2!$D$1:$UG$1))+(MONTH(DATEVALUE(Sheet3!C$1 &"1, 2000"))=MONTH(Sheet2!$C$1:$UF$1)))*Sheet2!$D$3:$UG$20)
that said, the way you have set up that table is problematic. the use of merged cells often causes issues.
a better approach overall would be to have a data entry sheet with name, region, date, and amount. so basically every entry is entered on a row instead of in a 2d table. Then a version of this input table could be a simple pivot table from the data set and the results you want could be another pivot table or you can filter the data set.

[#5]

What does ((MONTH(DATEVALUE(Sheet3!C$1 &"1, 2000"))=MONTH(Sheet2!$D$1:$UG$1))+(MONTH(DATEVALUE(Sheet3!C$1 &"1, 2000"))=MONTH(Sheet2!$C$1:$UF$1))) do?

[#6]

So the whole SUMPRODUCT is a series of checks that result in T/F (i.e. 1/0 values) that are combined to make any case that should be excluded 0 and any case to be included is 1 * the value. That part you copied out is a comparison of the MONTH listed at the top of this page/table:
(MONTH(DATEVALUE(Sheet3!C$1 &"1, 2000")))
with the corresponding month of the date at the top of the column for value being checked.
MONTH(Sheet2!$D$1:$UG$1)
BUT because you have those as merged cells the actual value in row 1 are like:
Jan 1, 0, Jan 2, 0, Jan 3, 0, etc...
so they have ZERO values for all the merged/hidden cells.
So I ADD the comparison of this month with the column top of the column BEFORE
so looking at Column E the is column #2 in the 'array' will compare with E1 in the 1st case (and be false because E1 is 0) and then compare with D1 in the second case and be true then by adding them 0 + 0 is false not included while either 1+0 or 0+1 would be true and is included.