Excel SumSq and SumIf

Quick question,

Is there any way to combine the SumSq function with SumIf?  I have stuff like this:

1    12
1    13
1     5
2    46
2    78
2    45

I'm trying to sum the squares of just "1" for example.  I could do it in another worksheet but it would be cooler if I could combine SUMSQ and SUMIF someway.  Not sure if there is any good way to do it though.
LVL 1
RogueStatAsked:
Who is Participating?
 
TracyConnect With a Mentor VBA DeveloperCommented:
Try using Sumproduct:

=SUMPRODUCT(($A$1:$A$6=1)*(1))*SUMSQ(1)
=SUMPRODUCT(($A$1:$A$6=2)*(1))*SUMSQ(2)
=SUMPRODUCT(($A$1:$A$6=3)*(1))*SUMSQ(3)
etc.

See attached.
Book2.xls
0
 
RogueStatAuthor Commented:
Nice idea, thank you.
0
 
RogueStatAuthor Commented:
FYI to other who might read this, I modified the code a little to reflect squaring a second column:

=SUMPRODUCT((H13:H18=1)*1,(I13:I18)^2) etc
0
All Courses

From novice to tech pro — start learning today.