SUMIF criteria using the AND function

**BobTheRocker** · 05-28-2011, 07:14 AM

I am using this formula in a spreadsheet

=SUMIF(A6:A45,"<>",(B6:B45))

If A6 to A45 contains data it sums the corresponding numbers in B6 to B45. I would also like to only sum the numbers that are smaller than 1. I thought I could use the AND function combined with SUMIF but cannot get it to work. Something like this

=SUMIF('PUPIL DATA'!$A$6:$A$45,AND("<>",<1)),(CW$117:CW$155)))

Any ideas/help greatly appreciated.

**Ron Coderre** · 05-28-2011, 07:27 AM

I need a clarification...are you looking to:
• sum Col_CW values on another sheet
where the corresponding Col_A cells on the current sheet:
• are not blank
• are less than 1
?

**zbor** · 05-28-2011, 07:47 AM

Try SUMPRODUCT function:

=SUMPRODUCT(--('PUPIL DATA'!$A$6:$A$45<>""),--('PUPIL DATA'!$A$6:$A$45<1),CW$117:CW$155))

BUT your ranges must be same:

6-45
117-155

is not same...