Home 
Search 
Today's Posts 
#1




Sum of squares if condition
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 
#2




Sum of squares if condition
"jeffrey" 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. 
#3




Sum of squares if condition
=SUMPRODUCT((A1:A4=1),(B1:B4)^2)  Jim Cone Portland, Oregon USA http://excelusergroup.org/media/ (Formats & Styles xl addin: lists/removes unused styles & number formats) "jeffrey" wrote in message ... 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 
#4




Sum of squares if condition
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" wrote: =SUMPRODUCT((A1:A4=1),(B1:B4)^2)  Jim Cone Portland, Oregon USAhttp://excelusergroup.org/media/ (Formats & Styles xl addin: *lists/removes unused styles & number formats) "jeffrey" wrote in ... 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 
#5




Sum of squares if condition
=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" 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
football squares  Excel Discussion (Misc queries)  
chi squares  Excel Discussion (Misc queries)  
blue squares  New Users to Excel  
performing least squares  Excel Discussion (Misc queries)  
removing squares and lines in squares that really should be paragr  Excel Discussion (Misc queries) 