Multiply 3 criteria with 2 conditions and sum the results for multiple columns.

[Doug_B]

I'm currently using a helper column with this math, which works. Then I sum the results of the helper column to get my result.

=IF(AND(C15="Sell Put",M15="Open"),F15*100*E15,"")

C15 & M15 = Criteria
F15 & E15 & 100 = Multiplication factors (contracts x 100 x $/contract)

I found a SUMIFS function for basic sums of rows with multiple criteria, which is fantastic, and I was able to eliminate 2 of my 3 helper columns. Is there a way to do similarly, but while within each row: multiply values of 2 different columns and a constant (3 items), and include 2 criteria?

This would be using basic Excel. I could probably use VBA in a pinch, but it's been years since I've done that. What I have works, just wondering if there's something I could use.

Thank you very much.

 

[Muhammad_Usman]

This is easy if your share screen shot of your data with input your raw data and output with manual you want

 

[kvsrinivasamurthy]

Range row 15:25 is taken. Change the range as required. No helper column required.

=SUMPRODUCT((C15:C25="Sell Put")*(M15:M25="Open")*(F15:F25)*(E15:E25))*100

 

[Doug_B]

This is easy if your share screen shot of your data with input your raw data and output with manual you want

Thank you for offering. I was able to figure out the answer using comments of another response, so I don't need to go further, but I appreciate your offer.

 

[Doug_B]

Range row 15:25 is taken. Change the range as required. No helper column required.

=SUMPRODUCT((C15:C25="Sell Put")*(M15:M25="Open")*(F15:F25)*(E15:E25))*100

Thank you very much. Your answer didn't work completely, but it led me to what I needed. I wasn't aware of the SUMPRODUCT function, which is another fabulous tool! What is working for me, is this:

=SUMPRODUCT(--(C12:C111="Sell Put"),--(M12:M111="Open"),(F12:F111)*100*(E12:E111))

 

[Doug_B]

Thank you very much. Your answer didn't work completely, but it led me to what I needed. I wasn't aware of the SUMPRODUCT function, which is another fabulous tool! What is working for me, is this:

=SUMPRODUCT(--(C12:C111="Sell Put"),--(M12:M111="Open"),(F12:F111)*100*(E12:E111))

By the way, I went back and tried the original solution, and for some reason it's working now.

=SUMPRODUCT((C12:C111="Sell Put")*(M12:M111="Open")*(F12:F111)*100*(E12:E111))