STDEV with multiple criteria with blank cells

[bluesky6688]

Hello all,

I have trouble with calculating standard error with multiple criteria. Below is the formula I used:

=STDEV(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,M2:M25))))/SQRT(COUNTIFS($J$2:$J$25,"A",$K$2:$K$25,"B",$L$2:$L$25,1,M2:M25,"<>"))

The problem is the blank cells in the range that I am calculating (M2:M25). Everything is alright if there were no blank cells in M2:M25. If there were any blank cells, however, the STDEV was wrong (the countifs part was correct). It treated the blank cells as zero.

Thanks for your help and wish you a Merry Christmas!

 

[AlphaFrog]

Add a criteria to test for blanks in M2:M25

=STDEV(IF(($J$2:$J$25="A")*($K$2:$K$25="B")*($L$2:$L$25=1)*($M$2:$M$25<>""),M2:M25,""))
/SQRT(COUNTIFS($J$2:$J$25,"A",$K$2:$K$25,"B",$L$2:$L$25,1,M2:M25,"<>"))

 

[Aladin Akyurek]

Why not prefer...

Control+shift+enter, not just enter:

=STDEV(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,IF(ISNUMBER(M2:M25),M2:M25)))))/
SQRT(SUM(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,IF(ISNUMBER(M2:M25),M2:M25))))))

as the calc involves only numbers?

 

[bluesky6688]

Add a criteria to test for blanks in M2:M25

=STDEV(IF(($J$2:$J$25="A")*($K$2:$K$25="B")*($L$2:$L$25=1)*($M$2:$M$25<>""),M2:M25,""))
/SQRT(COUNTIFS($J$2:$J$25,"A",$K$2:$K$25,"B",$L$2:$L$25,1,M2:M25,"<>"))

This works exactly as I wanted. Thanks!

 

[bluesky6688]

Why not prefer...

Control+shift+enter, not just enter:

=STDEV(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,IF(ISNUMBER(M2:M25),M2:M25)))))/
SQRT(SUM(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,IF(ISNUMBER(M2:M25),M2:M25))))))

as the calc involves only numbers?

This works if I change "SUM" to "COUNT". Thanks!

 

[Aladin Akyurek]

This works if I change "SUM" to "COUNT". Thanks!

That's Ok, although I meant to jot down:

=STDEV(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,IF(ISNUMBER(M2:M25),M2:M25)))))/
SQRT(SUM(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,IF(ISNUMBER(M2:M25),1))))))

 

[bluesky6688]

That's Ok, although I meant to jot down:

=STDEV(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,IF(ISNUMBER(M2:M25),M2:M25)))))/
SQRT(SUM(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,IF(ISNUMBER(M2:M25),1))))))

I see... There are so many ways to do it. Thanks again!