# Sumproduct & if formulas

#### PaulStafford

##### New Member
Please can someone help? - having a bit of a nightmare using the sumproduct formula...

I have several columns of data:
• Column D: There are 5 possible input options here (B2B, B2C - Live, B2C - Lab, B2C - Incubator, B2C - Consultancy)
• Column E: A margin expected from a client is entered here (any number between 0-100%)
• Column F: A probability factor (any number between 0-100%)
• Columns H-S: Months of the year (April - March).
There are numerous rows of data - each row is a new potential client. For each client, columns D, E, F and H-S are completed. In rows H-S, I have input an expected sales figure for each client.

What I need to do is:
• Work out the expected revenue for all input options in column D that have a 100% probability factor. At present the formula looks like: =SUMPRODUCT(--('New Business Opportunities'!\$F\$27:\$F\$158=1),'New Business Opportunities'!H\$27:H\$158). But I think I need to put something in front of it that says IF the rows in Column D have a 'B2B' in them, then do the formula above... I'm not sure how to do this??
• I then need to do the same as above, but in all cases where the probability in column F is not 100%. Again, I'm not sure how to do this??
• Finally, and even more complicated I think - I need to do the above formulas, but then multiply the results for each row, by the margin in column E. So, for example... I will want to calculate the expected contribution for all B2B clients who don't have a 100% probability (if column D says 'B2B', then multiply column E by column F, by column H for the month of April - and then add this up for all applicable rows).
If anyone can help, it would be fantastic.

THANKS!

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### sanrv1f

##### MrExcel MVP
Hope I understood ur requirement correctly

Assume the below to be the sample data set

just enter the below formula

H17
=SUMPRODUCT(--(\$D\$2:\$D\$15=\$D17),--(\$F\$2:\$F\$15=\$F17),H\$2:H\$15)

H18
=SUMPRODUCT(--(\$D\$2:\$D\$15=\$D18),--(\$F\$2:\$F\$15<>\$F18),H\$2:H\$15)

H19
=SUMPRODUCT(--(\$D\$2:\$D\$15=\$D19),--(\$F\$2:\$F\$15<>\$F19),H\$2:H\$15,\$F\$2:\$F\$15,\$E\$2:\$E\$15)

and fill right to the columns I-S

#### PaulStafford

##### New Member
Sankar,

Thanks very much for this - really helpful.

The only part I could do with help in changing is the formula in cell H18 in your example. I would like this to calculate the data in column H multiplied by the probability in column F, and then all added together. The way the calculation currently works is that it adds up all of column H - I would like it to add up (column H multiplied by column F). This works fine for the formula in cell H17 - but this is because all of the probabilities are 100%.

Hope this makes sense - thanks for your help.

#### barry houdini

##### MrExcel MVP
Hello Paul,

You can simply adjust Sankar's 2nd suggested formula along the lines of the 3rd, i.e. just add the extra range as another argument in SUMPRODUCT....

=SUMPRODUCT(--(\$D\$2:\$D\$15=\$D18),--(\$F\$2:\$F\$15<>\$F18),H\$2:H\$15, F\$2:F\$15)

Replies
4
Views
309
Replies
0
Views
199
Replies
6
Views
266
Replies
2
Views
106
Replies
3
Views
237

1,190,917
Messages
5,983,576
Members
439,852
Latest member
balasat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back