SUMPRODUCT with multiple column criteria

[mmunoz20]

Hi,

I have this formula, but it is not working. Any advice is welcome!

=SUMPRODUCT((database!$B$2:$B$<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">7=$B$1)*(database!$P$2:$P$7=<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">"AA")*((database!$M$2:$M$7>=$B$<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">2)*(database!$M$2:$M$7<=$C$2))<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">*(database!$N$2:$N$7))

main concerns on the column $N$2:$N$7
"AA"
"BB"
so the formula would be finding the number of clients in a particular school (e.g., school A=$B$1) and a particular time period (>='Graphs - Per school per period'!$B$2 & <='Graphs - Per school per period'!$C$2) that listed under the main concern column (N:N) that it as "AA" reason
I am having trouble creating a formula that would encompass all that criteria. I attempted that above formula, but it did not give me that accurate count.

 

[Aladin Akyurek]

Which range must meet either "AA" or "BB"? The range database!$P$2:$P$7 or database!$N$2:$N$7?

 

[mmunoz20]

Which range must meet either "AA" or "BB"? The range database!$P$2:$P$7 or database!$N$2:$N$7?

Thank you!! Your question helped me figure out my mistake! This is the right formula:

=SUMPRODUCT((database!$B$2:$B$<wbr style="color: rgb(87, 65, 35); font-family: arial, sans-serif; font-size: 12.800000190734863px;">7=$B$1)*(database!$P$2:$P$7=<wbr style="color: rgb(87, 65, 35); font-family: arial, sans-serif; font-size: 12.800000190734863px;">"AA")*((database!$M$2:$M$7>=$B$<wbr style="color: rgb(87, 65, 35); font-family: arial, sans-serif; font-size: 12.800000190734863px;">2)*(database!$M$2:$M$7<=$C$2)))

Thanks so much!!!

 

[Aladin Akyurek]

Thank you!! Your question helped me figure out my mistake! This is the right formula:

=SUMPRODUCT((database!$B$2:$B$<wbr style="color: rgb(87, 65, 35); font-family: arial, sans-serif; font-size: 12.800000190734863px;">7=$B$1)*(database!$P$2:$P$7=<wbr style="color: rgb(87, 65, 35); font-family: arial, sans-serif; font-size: 12.800000190734863px;">"AA")*((database!$M$2:$M$7>=$B$<wbr style="color: rgb(87, 65, 35); font-family: arial, sans-serif; font-size: 12.800000190734863px;">2)*(database!$M$2:$M$7<=$C$2)))

Thanks so much!!!

That's great. Why not invoke a COUNTIFS formula which is faster if you have that function?

=COUNTIFS(database!$B$2:$B$<wbr style="font-family: arial,sans-serif; font-size: 12.8px;">7,$B$1,database!$P$2:$P$7=<wbr style="color: rgb(87, 65, 35); font-family: arial, sans-serif; font-size: 12.800000190734863px;">"AA",database!$M$2:$M$7,">="&$B$<wbr style="color: rgb(87, 65, 35); font-family: arial, sans-serif; font-size: 12.800000190734863px;">2,database!$M$2:$M$7,"<="&$C$2)