SUMIF criteria using the AND function

I am using this formula in a spreadsheet

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Any ideas/help greatly appreciated.

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