J

#### jeffrey

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

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

J

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

J

jeffrey said: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.

J

--

Jim Cone

Portland, Oregon USA

http://excelusergroup.org/media/

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

"jeffrey" <[email protected]>

wrote in message

news:a8efeca8-aff1-4f1e-9833-cc5d47ca6465@p29g2000pre.googlegroups.com...

J

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

J

CountIfs treat blank cells as a 0 value.

--

Jim Cone

Portland, Oregon USA .

http://www.mediafire.com/PrimitiveSoftware .

(free and commercial excel programs)

"jeffrey" <[email protected]>

wrote in message

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