Remember Me? July 21st 11, 11:33 PM posted to microsoft.public.excel.worksheet.functions
 jeffrey external usenet poster First recorded activity by ExcelBanter: Jun 2011 Posts: 8 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 July 21st 11, 11:48 PM posted to microsoft.public.excel.worksheet.functions
 joeu2004[_2_] external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 829 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

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. July 21st 11, 11:52 PM posted to microsoft.public.excel.worksheet.functions
 Jim Cone[_2_] external usenet poster First recorded activity by ExcelBanter: Apr 2008 Posts: 1,549 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 July 22nd 11, 07:39 AM posted to microsoft.public.excel.worksheet.functions
 jeffrey external usenet poster First recorded activity by ExcelBanter: Jun 2011 Posts: 8 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 July 22nd 11, 12:15 PM posted to microsoft.public.excel.worksheet.functions
 Jim Cone[_2_] external usenet poster First recorded activity by ExcelBanter: Apr 2008 Posts: 1,549 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post CRS Excel Discussion (Misc queries) 7 November 3rd 08 10:17 PM Rozie Excel Discussion (Misc queries) 1 June 29th 06 09:52 PM blue New Users to Excel 1 February 14th 06 09:46 AM Darius Blaszijk Excel Discussion (Misc queries) 5 March 19th 05 07:25 PM finnadat Excel Discussion (Misc queries) 5 February 10th 05 11:12 PM

All times are GMT +1. The time now is 10:50 AM. Copyright ©2004-2021 ExcelBanter.