# how do i write a sumproduct formula to read within a range?

#### Marq

##### Well-known Member
Using Excel 2010

in cell BA9 I want a sumproduct formula to basically say:

Look in column "C" for the word undone.....then look in column G for 100%.....then look in column AV for all cells that have a value between 0 to 500....once all of that criteria is met sum up the hours in column F

so basically the total hours for all jobs that are: 100% & undone & are in between 0 to 500.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### SNelson929

##### New Member
I am not certain you need a sumproduct. It sounds like you just need a conditional sum to add up column f, if your conditions in columns C, G, and AV are met.

=+SUMIFS(\$F\$3:\$F\$8,\$C\$3:\$C\$8,"Undone",\$g\$3:\$g\$8,100%,\$av\$3:\$av\$8,">=0",\$av\$3:\$av\$8,"<=500")

Maybe you are multiplying column G x column AV x column F. If so, here is that formula.

+sumproduct(if(\$C\$3:\$C\$8="Done",if(\$g\$3:\$g\$8=100%,if(\$av\$3:\$av\$8>=0,if(\$av\$3:\$av\$8<=500,\$g\$3:\$g\$8)))),if(\$C\$3:\$C\$8="Done",if(\$g\$3:\$g\$8=100%,if(\$av\$3:\$av\$8>=0,if(\$av\$3:\$av\$8<=500,\$av\$3:\$av\$8)))),if(\$C\$3:\$C\$8="Done",if(\$g\$3:\$g\$8=100%,if(\$av\$3:\$av\$8>=0,if(\$av\$3:\$av\$8<=500,\$f\$3:\$f\$8)))))

***Remember, this is an array function, so you need to Ctrl-Shift-Enter after you put in the formula

Good luck

#### Teeroy

##### Well-known Member
SUMPRODUCT can be used similarly to the SUMIFS above, which is very useful if you are using an excel version prior to 2007.

=SUMPRODUCT((C\$2:C\$1000="Undone")*(G\$2:G\$1000=1)*(AV\$2:AV\$1000<=500)*(AV\$2:AV\$1000>=0)*(F\$2:F\$1000))

confirm with enter.

Just remember that the test for "Undone" is case sensitive so it is a good idea to have data validation on this column.

#### Marq

##### Well-known Member
I am not certain you need a sumproduct. It sounds like you just need a conditional sum to add up column f, if your conditions in columns C, G, and AV are met.

=+SUMIFS(\$F\$3:\$F\$8,\$C\$3:\$C\$8,"Undone",\$g\$3:\$g\$8,100%,\$av\$3:\$av\$8,">=0",\$av\$3:\$av\$8,"<=500")

Maybe you are multiplying column G x column AV x column F. If so, here is that formula.

+sumproduct(if(\$C\$3:\$C\$8="Done",if(\$g\$3:\$g\$8=100%,if(\$av\$3:\$av\$8>=0,if(\$av\$3:\$av\$8<=500,\$g\$3:\$g\$8)))),if(\$C\$3:\$C\$8="Done",if(\$g\$3:\$g\$8=100%,if(\$av\$3:\$av\$8>=0,if(\$av\$3:\$av\$8<=500,\$av\$3:\$av\$8)))),if(\$C\$3:\$C\$8="Done",if(\$g\$3:\$g\$8=100%,if(\$av\$3:\$av\$8>=0,if(\$av\$3:\$av\$8<=500,\$f\$3:\$f\$8)))))

***Remember, this is an array function, so you need to Ctrl-Shift-Enter after you put in the formula

Good luck

the first example you gave worked...thanks..(sorry it took so long to respond...i asked the question yesterday right when i left work....did not get to work till late this morning and had a chance to implement the formula.)

Replies
1
Views
280
Replies
7
Views
108
Replies
11
Views
322
Replies
0
Views
537
Replies
5
Views
350

1,195,582
Messages
6,010,579
Members
441,557
Latest member
Jbest23

### 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