Sum of squares if condition

  • Thread starter Thread starter jeffrey
  • Start date Start date
J

jeffrey

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
 
jeffrey said:
is there a SUMSQIF function? [....]
I want to sum the squares of ColB, if ColA=1
Click to expand...

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.
 
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
 
=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" <[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
 
Back
Top