Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of squares if condition
=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" 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 add-in: *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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |