Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Sum of squares if condition

1,733 views
Skip to first unread message

jeffrey

unread,
Jul 21, 2011, 6:33:02 PM7/21/11
to
I want to perform a sum of squares with conditions. I know there is a
SUMSQ function and a SUMIF function, but is there a SUMSQIF function?

I have a table like:
ColA ColB
1 3435
2 45345
2 230
1 9685

I want to sum the squares of ColB, if ColA=1

Jeff

joeu2004

unread,
Jul 21, 2011, 6:48:45 PM7/21/11
to
"jeffrey" <johje...@hotmail.com> wrote:
> is there a SUMSQIF function?
[....]

> I want to sum the squares of ColB, if ColA=1

AFAIK, there is none. None found using a Google search (hint!). But
whenever you ask a question like that, you need to specify the Excel
version(s) you are asking about.

Anyway, the alternative is the following __array_formula__ [*]:

=SUMSQ(IF(A1:A1000=1,B1:B1000))

[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter.
Excel will display an array formula surrounded by curly braces in the
Formula Bar, i.e. {=formula}. You cannot type the curly braces yourself.
If you make a mistake, select the cell, press F2 and edit, then press
ctrl+shift+Enter.

Jim Cone

unread,
Jul 21, 2011, 6:52:09 PM7/21/11
to

=SUMPRODUCT(--(A1:A4=1),(B1:B4)^2)
--
Jim Cone
Portland, Oregon USA
http://excelusergroup.org/media/
(Formats & Styles xl add-in: lists/removes unused styles & number formats)


"jeffrey" <johje...@hotmail.com>
wrote in message
news:a8efeca8-aff1-4f1e...@p29g2000pre.googlegroups.com...

jeffrey

unread,
Jul 22, 2011, 2:39:46 AM7/22/11
to
I am also trying to count cells where Column B is not null AND the
adjacent value in Column A is 1.

In the following example, I would like a count of 2.
A B
1 3214
2 23190
1
1 9876
2 6789

I tried using COUNTIFS(A:A,1,B:B,ISNUMBER(B:B)), but that returns
zero.

Jeff


On Jul 21, 3:52 pm, "Jim Cone" <james.cone...@comcast.netXxx> wrote:
> =SUMPRODUCT(--(A1:A4=1),(B1:B4)^2)
> --
> Jim Cone

> Portland, Oregon USAhttp://excelusergroup.org/media/


> (Formats & Styles xl add-in:  lists/removes unused styles & number formats)
>

> "jeffrey" <johjeff...@hotmail.com>
> wrote in messagenews:a8efeca8-aff1-4f1e...@p29g2000pre.googlegroups.com...

Jim Cone

unread,
Jul 22, 2011, 7:15:33 AM7/22/11
to
=SUMPRODUCT(--(A1:A40=1),--(B1:B40<>""))

CountIfs treat blank cells as a 0 value.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)

"jeffrey" <johje...@hotmail.com>
wrote in message

news:e60ac994-e9c5-495f...@u6g2000prc.googlegroups.com...

0 new messages