SumProduct Formula with subtraction function

[chad13]

In column A I have hourly rates and in column B I have hours. At the bottom of the column, I have a sumproduct formula: =sumproduct(a1:a10,b1:b10)
In one formula, can I maintain the sumproduct formula but add a function subtract a value, say $5.00, from every value in column A? I can't add another column so I'm trying to do it all in one formula.

Ex.
A B
$25.00 10
$22.00 12
$26.00 10

=sumproduct results in $774

I want the formula to take $5.00 from every value in column A then do the sumproduct formula. The result would be: $614.

I can do another sumproduct formula of the $5 by the hours and subtract that result from the first result but would like to see if there is another way to do it.

Any help would be greatly appreciated.

Thanks,

Chad

 

[sheetspread]

Excel 2010
	A	B
2	25	10
3	22	12
4	26	10
5
6		614
Sheet4

Cell Formulas
Range	Formula
B6	=SUMPRODUCT(A2:A4-5,B2:B4)

 

[chad13]

Fantastic! I'm not sure how I missed this simple solution. Exactly what I was looking for. Thanks.

 

[chad13]

This works perfectly for columns with no text. But I periodically have text items in various rows of the columns. How can I do the same formula so that it doesn't error out if there are text values.

Thanks.

 

[sheetspread]

This is one way:

Excel 2010
	A	B
1
2	25	10
3	j	12
4	26	10
5
6		410
Sheet4

Cell Formulas
Range	Formula
B6	{=SUM(IF(ISNUMBER(A2:A4),A2:A4-5)*B2:B4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

=SUM(IFERROR(A2:A4-5,0)*B2:B4) works too

 

[Aladin Akyurek]

This works perfectly for columns with no text. But I periodically have text items in various rows of the columns. How can I do the same formula so that it doesn't error out if there are text values.

Thanks.

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(A2:A4),A2:A4-5)*IF(ISNUMBER(B2:B4),B2:B4))

 

[markmzz]

This works perfectly for columns with no text. But I periodically have text items in various rows of the columns. How can I do the same formula so that it doesn't error out if there are text values.

Thanks.

Hi!

Another way (Array Formula):

Use Ctrl+Shift+Enter to enter the formula

=SUM(IFERROR((A1:A6-5)*B1:B6,0))

Markmzz