Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 21st 11, 11:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 8
Default 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   Report Post  
Old July 21st 11, 11:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 829
Default 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   Report Post  
Old July 21st 11, 11:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,549
Default 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   Report Post  
Old July 22nd 11, 07:39 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 8
Default 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   Report Post  
Old July 22nd 11, 12:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,549
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
football squares CRS Excel Discussion (Misc queries) 7 November 3rd 08 10:17 PM
chi squares Rozie Excel Discussion (Misc queries) 1 June 29th 06 09:52 PM
blue squares blue New Users to Excel 1 February 14th 06 09:46 AM
performing least squares Darius Blaszijk Excel Discussion (Misc queries) 5 March 19th 05 07:25 PM
removing squares and lines in squares that really should be paragr finnadat Excel Discussion (Misc queries) 5 February 10th 05 11:12 PM


All times are GMT +1. The time now is 10:50 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017