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

Hi

My name is André. I am an independent consultant.

>Okay, from what I understand what needs to be calculated, it is the quantity, at the value established in the main cell, and if you have nothing, ignore it.

I will assemble the formula here for you, at first it is not very complex.

André.

Answer 3

I made use of an auxiliary table to first calculate the values, and then I transposed it to the final table.

as there are several steps. I will share this spreadsheet with you so you can analyze it.

Answer 4

https://we.tl/t-D85IsIRrY1

Please see if it suits you. And let me know.

Answer 5

Thank you - I am looking for a solution that does it all in one step vs. splitting it into a new table. Is this possible?