Sumproduct Help

[mjmoon]

I currently have this formula that I would like to expand on to include the results of a sumproduct function.

=SUMIFS($F$4:$F$42,$C$4:$C$42,"Commercial",$D$4:$D$42,"Prospecting")+SUMIFS($G$4:$G$42,$C$4:$C$42.............

Where I have F4:F42, I would like the SUMIFS function to first calculate the sumproduct of (F4:F42,E4:E42) if the results in column C = "Commercial" and the results in column D = Prospecting".

In summary I bascially have 4 columns:
Column C can equal either Commerical, Ag or Other
Colulmn D can equal Prospecting, New or Unknown
Column E is labeled probability where the user enters a % from either 1 to 100
Collumn F is a dollar amount.

I want to know the total amount of Commerical accounts that we are prospecting based on the product of dollar amount times probability and so on for each scenario.

 

[barry houdini]

Perhaps you need to use SUMPRODUCT instead of SUMIFS?

=SUMPRODUCT($E$4:$E$42,$$4:$F$42,($C$4:$C$42="Commercial")*($D$4:$D$42="Prospecting"))

 

[mjmoon]

That is what I was looking for. THank you very much!