SUMIF with Multiple Criteria

[DPKologie]

I want to use SUMIF to add numbers together in a row only if they are between 65 and 90, inclusive. I was able to use SUMIF to add numbers in the row >=95, but the formula didn't work using "AND(>=65,<=90)" and seems to want to use only a single value. There must be a way to specify a range of values in the criteria portion..?!

Here is an example of the SUMIF formula that worked for >=95:

=ABS(ROUNDDOWN(1.5*(SUMIF($B$3:$D$3,">=95",$B$3:$D$3)+SUMIF($I$3,">=95",$I$3)+SUMIF($L$3:$N$3,">=95",$L$3:$N$3))+1.25*(SUMIF($E$3:$F$3,">=95",$E$3:$F$3)+SUMIF($J$3,">=95",$J$3)+SUMIF($O$3:$P$3,">=95",$O$3:$P$3))+(SUMIF($G$3:$H$3,">=95",$G$3:$H$3)+SUMIF($K$3,">=95",$K$3)+SUMIF($Q$3:$R$3,">=95",$Q$3:$R$3)),0))

Since substituting "AND(>=65,<=95)" didn't work, and the possible values are in increments of 5, I specified each possible value in a separate SUMIF statement (e.g. "65","70","75", "80","85", and "90") and that works, but it will make my formulas extremely long. As you can see I have 3 different sections I want multiplied by different amounts, and there are several ranges within each of those sections.

Any suggestions would be most helpful!

[Tony Valko]

This will sum the numbers in the range that are >=65 and <=90:

=SUMIFS($B$3:$D$3,$B$3:$D$3,">=65",$B$3:$D$3,"<=90")

[Ron Coderre]

I *think* this does what you want:

Please Login or Register  to view this content.

Is that something you can work with?