# Thread: STDEV, IF, AND instead of STDEVIFS Thanks: 0 Likes: 0

1. ## STDEV, IF, AND instead of STDEVIFS

Based on the posts I've reviewed I'm not the only one looking for a STDEVIFS function in Excel.

I'm trying to get along without it by using IF and AND. There are two criteria that need to be matched and if they match I want the STDEV. I am trying to get Excel to calculate STDEV for a specified flavour for each person. I've tried nesting AND inside an IF function but got a !VALUE error [ =STDEV(IF(AND(\$O\$3:\$O\$1000=G14,\$N\$3:\$N\$1000=F14),\$P\$3:\$P\$1000,"")) ] I used Ctrl+Shift+Enter but it still didn't work. Can any of you assist?

Column F has the reference names for each grouping and column G has the Flavour sub-grouping. Here is a sample of the data I'm pulling from:

 Col N Col O Col P Name Flavour Grams BB Plain 13.3 BB Caramel 14.1 NA Plain 13.6 NA PB 15.7 CS PB 16.3 CS Caramel 17.4

2. ## Re: STDEV, IF, AND instead of STDEVIFS

Your problem is AND will not produce an array of results. Try a nested if maybe:

=STDEV(IF(\$O\$3:\$O\$1000=G14,IF(\$N\$3:\$N\$1000=F14,\$P\$3:\$P\$1000)))

3. ## Re: STDEV, IF, AND instead of STDEVIFS

Yes! Thank you!