Excel - Multiply and sum two columns with numerous rows if another column contains text

Question

Hey all - posting a question here in hopes to get some help :). 

Let's say I have a table that looks like something below, columns Value 1, Value 2, andValue 3 will always be empty (I can change this to a 0 if it makes it easier) or a 1.

I want to multiply Count byValue 1 per row, and then add each of the rows that contain the Month of that specific Month in the next table (on a new sheet).

Sheet1

	A	B	C	D	E
1	Month	Count	Value 1	Value 2	Value 3
2	January	4	1	1
3	January	3		1	1
4	January	2	1	1	1
5	January	1	1		1
6	January	2	1
7	January	2			1
8	January	1		1	1
9	February	3	1
10	February	1		1
11	February	1		1
12	February	2	1		1
13	February	3
14	March	1	1	1	1
15	March	2			1
16	March	3		1

I'd like to populate a new table on a different sheet with values that look something like this:

Sheet2

	A	B	C	D
1	Month	Value 1 Total	Value 2 Total	Value 3 Total
2	January	9	10	9
3	February	5	2	2
4	March	1	4	3

I've tried a few things but cannot get them to work. Examples below:

For the Value 1 Total (cell B2):

• =IF(Sheet1!A:AA:A = "January",SUMPRODUCT(Sheet1!A:AB:B,Sheet1!A:AC:C), "")
• =SUMPRODUCT(SUMIF(Sheet1!A:A, A2, Sheet1!A:AB:B),SUMIF(Sheet1!A:AA:A,A2,Sheet1!A:AC:C))
• =SUMPRODUCT(Sheet1!A:AB:B,Sheet1!A:AC:C*(Sheet1!A:AA:A="January"))

Answer 1

I used a Table and structured references, but you can change that to regular addressing if you preferred.

Formula

  B2:  =SUMPRODUCT((mnthTbl[[Month]:[Month]]=$A2)*mnthTbl[[Count]:[Count]]*mnthTbl[Value 1])

Fill Down and across.

(With the mix of absolute and mixed references, the appropriate references will self-adjust as you fill down and across).

Original Data => Table Name:  mnthTbl

Results

Answer 2

Sorry I am a pretty new to Excel, I seem to be getting a #VALUE! error when trying your solution above. This is what my function looks like:

=SUMPRODUCT((Sheet1!A:A=A2)*Sheet1!B:B*Sheet1!C:C)

Answer 3

Using whole column references, you  are trying to multiply strings. Hence the error. Avoid whole column references. Or do it the way I demonstrated.

Answer 4

Fantastic - it worked! Thank you for your help!!

Answer 5

Yes, it is possible, I will try to combine this into a single formula. However, if you want to use it the way I did, you can create an auxiliary spreadsheet, and communicate between them.