#1   Report Post  
Old May 23rd 05, 08:24 PM
anand
 
Posts: n/a
Default sumproduct

I can create a sumproduct formula that refers to external cells for a
filtering function
e.g. =sumproduct(A1:A200=A205205)*(B1:B200=1).

this should return the sum of those # of rows where there is a 1 in row B
and any of 4 different values (which I can switch from text or numbers) in
the first 4 cells of row 205.

Question is how can I indicate either blanks or nonblanks in one of the 4
reference cells in row 205.

In addition, what does the operator "-" do when replacing the asterisk (*)
in the sumproduct formula. I know that a plus sign means "or" rather than
"and" for the asterisk. does the "-" symbol mean "but not"? So if I want a
formula that indicates, for example everyhing with a "apple" in column one
but not a "orange" in column 2?

  #2   Report Post  
Old May 23rd 05, 08:48 PM
Bob Phillips
 
Posts: n/a
Default


"anand" wrote in message
...
I can create a sumproduct formula that refers to external cells for a
filtering function
e.g. =sumproduct(A1:A200=A205205)*(B1:B200=1).


That should be

=SUMPRODUCT((A1:A200=A205205)*(B1:B200=1))

this should return the sum of those # of rows where there is a 1 in row B
and any of 4 different values (which I can switch from text or numbers) in
the first 4 cells of row 205.


Question is how can I indicate either blanks or nonblanks in one of the 4
reference cells in row 205.


What exactly do you mean? It works with blanks in A1:A200, or in A205205

In addition, what does the operator "-" do when replacing the asterisk (*)
in the sumproduct formula. I know that a plus sign means "or" rather than
"and" for the asterisk. does the "-" symbol mean "but not"?


No, the - goes with another, a double unary, which is used to coerce the
True/False results of a test are coerced to 1/0 values, for the arithmetic
operations.

So if I want a
formula that indicates, for example everyhing with a "apple" in column one
but not a "orange" in column 2?


No, that is a simple <

=SUMPRODUCT(--(A1:A200="apple),--(B1:B200<"orange"))

equivalent to


=SUMPRODUCT((A1:A200="apple)*(B1:B200<"orange"))


  #3   Report Post  
Old May 23rd 05, 08:53 PM
Ola
 
Posts: n/a
Default

First:
"Question is how can I indicate either blanks or nonblanks in one of the 4
reference cells in row 205."
I presume you mean -exclude cells with blanks? In that case
=SUMPRODUCT((A1:A200=A205205)*(B1:B200=1)*(A205: D205<0))

Second:
I think you mean --
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Hope it helped
Ola Sandström

  #4   Report Post  
Old May 23rd 05, 10:16 PM
Aladin Akyurek
 
Posts: n/a
Default

anand wrote:
I can create a sumproduct formula that refers to external cells for a
filtering function
e.g. =sumproduct(A1:A200=A205205)*(B1:B200=1).

this should return the sum of those # of rows where there is a 1 in row B
and any of 4 different values (which I can switch from text or numbers) in
the first 4 cells of row 205.

Question is how can I indicate either blanks or nonblanks in one of the 4
reference cells in row 205.

In addition, what does the operator "-" do when replacing the asterisk (*)
in the sumproduct formula. I know that a plus sign means "or" rather than
"and" for the asterisk. does the "-" symbol mean "but not"? So if I want a
formula that indicates, for example everyhing with a "apple" in column one
but not a "orange" in column 2?


=SUMPRODUCT(--ISNUMBER(MATCH($A$1:$A$200,$A$205:$D$205,0)),--($B$1:$B$200=1))


  #5   Report Post  
Old May 23rd 05, 11:12 PM
anand
 
Posts: n/a
Default

No, what I'm really trying to do is creat a series of external references for
a dozens of sumproduct formula. Then copy the formulas whole and duplicate
(with a fixed data block). The sumproduct formulas should give identical
results (presuming that I copy over the reference cells also. However, now
what I'd want to do is adjust the reference cells so that I'm looking at a
different set of conditions. In some cases, I may want to include either a
blank, all nonblanks or mabe all. Any idea how to use a reference cell to
indicate this? I would assume if I'm using a series of reference cells in a
row linked by "or" as this function works, then adding something than
indicates blanks, blanks, or all could be done; just not sure how. If used
all or non-blanks, this would likey over-ride any selection in rest of
reference range i.e. if I have 1,2,3,4 as acceptable conditions and replace
with 1,2,3,all, then I would presume that the 1,2,3 become irrelevant.

So question remains, is there any symbol that can indicate blanks,
non-blanks or all within a reference cell attached to a sumproduct function?

"Ola" wrote:

First:
"Question is how can I indicate either blanks or nonblanks in one of the 4
reference cells in row 205."
I presume you mean -exclude cells with blanks? In that case
=SUMPRODUCT((A1:A200=A205205)*(B1:B200=1)*(A205: D205<0))

Second:
I think you mean --
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Hope it helped
Ola Sandström



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
Sumproduct Peter B Excel Worksheet Functions 1 March 7th 05 01:59 PM
sumproduct causing memory errors? dave Excel Worksheet Functions 1 March 3rd 05 09:31 AM
Can I reference =, <, or > sign in SUMPRODUCT BobT Excel Discussion (Misc queries) 7 February 16th 05 01:58 PM
Sumproduct function not working Scott Summerlin Excel Worksheet Functions 12 December 4th 04 05:15 AM
adding two sumproduct formulas together ski2004_2005 Excel Worksheet Functions 1 November 12th 04 09:08 PM


All times are GMT +1. The time now is 12:29 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017