=sumproduct

[GigEm]

My goal is to sum the product of two numbers on a data entry sheet, given the entry meets certain criteria, and display the answers on a sum sheet separated by size and material type. Here is the formula so far...

=SUMPRODUCT(--('Piping Takeoff'!J15:J24="bwsw"),--('Piping Takeoff'!H15:H24=0),--('Piping Takeoff'!K15:K24=0.5),'Piping Takeoff'!K15:K24*'Piping Takeoff'!M15:M24)

J15:J24 is weld type
H15:H24 is the material type
K15:K24 is the pipe size

The formula works as is, but I need to change one thing for it to suit my needs. Rather than summing data if the weld type (J15:J24) ="bwsw", I need it to sum the data if the weld type matches any of a list (Y2:Y59) of text strings, i.e. "bwsw", "bwxs", "swxs".

Substituting "bwsw" for Y2:Y59 does not work.
=SUMPRODUCT(--('Piping Takeoff'!J15:J24=Y2:Y59),--('Piping Takeoff'!H15:H24=0),--('Piping Takeoff'!K15:K24=0.5),'Piping Takeoff'!K15:K24*'Piping Takeoff'!M15:M24)

I realize I could include all 57 of the weld types with "" and ,'s but I'm looking for the proper way to include a list in the =sumproduct function.

Thanks.

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--ISNUMBER(MATCH('Piping Takeoff'!J15:J24,Y2:Y59,0)),--('Piping Takeoff'!H15:H24=0),--('Piping Takeoff'!K15:K24=0.5),'Piping Takeoff'!K15:K24,'Piping Takeoff'!M15:M24)

 

[hiker95]

GigEm,

Maybe this will give you an idea:

=SUMPRODUCT(--($D$5:$D$162={"cri","maj","enh","min"})*($J$5:$J$162="DataContent")*($Y$5:$Y$162))

 

[GigEm]

Try...

=SUMPRODUCT(--ISNUMBER(MATCH('Piping Takeoff'!J15:J24,Y2:Y59,0)),--('Piping Takeoff'!H15:H24=0),--('Piping Takeoff'!K15:K24=0.5),'Piping Takeoff'!K15:K24,'Piping Takeoff'!M15:M24)

That is exactly what I needed. Thank you. I had just used that recently too!

 

[Aladin Akyurek]

That is exactly what I needed. Thank you. I had just used that recently too!

You are welcome. Thanks for providing feedback.