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

[Marq]

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.

 

[SNelson929]

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]

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]

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.)