SUM with SUBTOTAL for STDEV

[SRafter]

Good Morning, I hope someone out there can help.

I'm trying to determine the stdev from a range of numbers where there is a name entered multiple times in another range.

i.e. range B26:B1999 has the names, D26:D1999 has the numbers.

I want to sum the numbers for each name and then find the STDEV across the totals. Can someone help??? Cheers.

 

[jeffreybrown]

Hello & Welcome to the Board,

Try...

=STDEV(IF($B$26:$B$1999="Name",$D$26:$D$1999))

IMPORTANT
This is an array formula
Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually

 

[SRafter]

Thanks Jeff, your suggestion works well when I need the STDEV for only one person, however, I have multiple people with multiple entries that can be filtered by month & campaign.

I need to SUM all the number entries for each person and then find the STDEV across the totals.

eg - 12 people made sales in July across 6 different campaigns. I need the STDEV for total sales by person for July.

Hope you can help. Cheers.

 

[Aladin Akyurek]

Thanks Jeff, your suggestion works well when I need the STDEV for only one person, however, I have multiple people with multiple entries that can be filtered by month & campaign.

I need to SUM all the number entries for each person and then find the STDEV across the totals.

eg - 12 people made sales in July across 6 different campaigns. I need the STDEV for total sales by person for July.

Hope you can help. Cheers.

If you don't want to create totals for each person in a separate area and run STDEV on the totals so created, add the following code as a module to your workbook:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function


________________________________________
Now we can invoke...

Control+shift+enter, not just enter:

=STDEV(IF(V(SUMIF($B$26:$B$1999,
   IF(FREQUENCY(IF($B$26:$B$1999<>"",
     MATCH($B$26:$B$1999,$B$26:$B$1999,0)),
      ROW($B$26:$B$1999)-ROW($B$26)+1),$B$26:$B$1999),
        $D$26:$D$1199)),V()))

 

[SRafter]

Worked a treat !!!! thanks for your help.

Cheers.

 

[Aladin Akyurek]

Worked a treat !!!! thanks for your help.

Cheers.

You are welcome. Thanks for providing feedback.