Sumproduct and satisfying multiple criteria

[EliseFreedman]

I am trying to find out if each of the rows in my spreadsheet satisfy a combination of criteria. The first of those is whether the date in the row falls within a chosen date range and also whether it satisfies another criteria for example a referral type. I am only interested if the row satisfies all the criteria.

I was using the formula

=SUMPRODUCT(--(I2:I2>=$A$21),--(I2:I2<=$A$22),--(M2:M2=$A$15))

I2 is the cell with the date in it, A21 is the cell with the start date for the search, A22 is the cell containing the end date for the search, M2 is the cell specifying the type of referral for that record and A15 is the cell where you type in which referral you wish to serach for.

Even though the row meets all of the criteria I am still getting a value of 0. What am I doing wrong?

 

[bluedragon2]

If you are just comparing one cell for the criteria and not an array:

=IF(AND(I2>=$A21,I2<=$A$22),$A$15,"")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Hi,

"...if each of the rows in my spreadsheet satisfy a combination of criteria..."

Why are you using ABSOLUTE references?

Why are you CRITERIA RANGES only one cell (I2 & M2)?

Why is the criteria for M2 in row 15, while the criteria for I2 (presumably dates) are rows 21 & 22?

Maybe A15 has a static value while each row has a distinct date?

Maybe more like...
[tt]
=SUMPRODUCT(--(I16:I9999>=A21)*(I16:I9999<=A22)*(M16:M9999=$A$15))
[/tt]
assuming that your table is from row 16 thru row 9999


Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}