STDEV, IF, AND instead of STDEVIFS

[Nanaia]

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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Col N
[/TD]
[TD]Col O
[/TD]
[TD]Col P
[/TD]
[/TR]
[TR]
[TD]Name
[/TD]
[TD]Flavour
[/TD]
[TD]Grams
[/TD]
[/TR]
[TR]
[TD]BB
[/TD]
[TD]Plain
[/TD]
[TD]13.3
[/TD]
[/TR]
[TR]
[TD]BB
[/TD]
[TD]Caramel
[/TD]
[TD]14.1
[/TD]
[/TR]
[TR]
[TD]NA
[/TD]
[TD]Plain
[/TD]
[TD]13.6
[/TD]
[/TR]
[TR]
[TD]NA
[/TD]
[TD]PB
[/TD]
[TD]15.7
[/TD]
[/TR]
[TR]
[TD]CS
[/TD]
[TD]PB
[/TD]
[TD]16.3
[/TD]
[/TR]
[TR]
[TD]CS
[/TD]
[TD]Caramel
[/TD]
[TD]17.4
[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

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

 

[Nanaia]

Yes! Thank you!