Multiplying two cells together then summing them together if three criteria are met.

[msheofsky]

I have a database with auto generated data. On a separate sheet in the same workbook I can pull the information I need with SUMIFS but in a certain situation I need to multiply two cells first then sum the answers together without adding another column and divide by 12 to get Feet.

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, show the expected results in the second sheet.

 

[msheofsky]

Mr Excel Help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Print Name	System	Type	Qty	CL Inches per Item
2	M101	MP Supply	Rect.	1	125
3	M101	LP Supply	Rect.	1	325
4	M101	MP Supply	Rnd.	1	125
5	M101	MP Supply	Rect.	5	325		Items in the box to the left are auto generated by a program (Database)
6	M201	MP Supply	Rect.	1	33
7	M201	MP Supply	Rect.	1	150
8	M201	MP Supply	Rect.	11	554			These must be multiplied together before summing together.
9	M201	MP Supply	Rect.	1	32
10	M201	MP Supply	Rect.	1	66
11	M201	MP Supply	Rnd.	1	964
12	M201	MP Return	Rnd.	2	12559
13	M201	MP Exhaust	Rnd.	2	255
14	M201	MP Supply	Rnd.	1	28866										Answer should be 145.8333333333
15
16								Total Feet
17						M101
18							MP Supply Rect.	37.5
19							MP Supply Rnd	10.41667			The Error with my formula is there is a quanity in column D that is not represented in column E.
20						M201
21							MP Supply Rect.	69.58333
22							MP Supply Rnd	2485.833
23															Answer should be 528.5
24
25
26
27
28
29
30
31
32
Sheet1

Cell Formulas
Range	Formula
H18	H18	=SUMIFS(E2:E14,A2:A14,F17,B2:B14,"MP Supply",C2:C14,"Rect.")/12
H19	H19	=SUMIFS(E2:E14,A2:A14,F17,B2:B14,"MP Supply",C2:C14,"Rnd.")/12
H21	H21	=SUMIFS(E2:E14,A2:A14,F20,B2:B14,"MP Supply",C2:C14,"Rect.")/12
H22	H22	=SUMIFS(E2:E14,A2:A14,F20,B2:B14,"MP Supply",C2:C14,"Rnd.")/12

 

[msheofsky]

The problem with my formula is that there is a quantity of (5) items on D5 but the amount on E5 accounts for only (1) of them. Therefore, I need D5 multiplied by E5 before it is summed up if all the criteria are met.

 

[Fluff]

How about

=SUMPRODUCT((A2:A14=F17)*(B2:B14="MP Supply")*(C2:C14="Rect."),D2:D14,E2:E14)/12

 

[msheofsky]

How about

=SUMPRODUCT((A2:A14=F17)*(B2:B14="MP Supply")*(C2:C14="Rect."),D2:D14,E2:E14)/12

Brilliant!
Thank you sooooooooo much!

 

[Fluff]

You're welcome & thanks for the feedback.


I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)