Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rhiannons_Wish
 
Posts: n/a
Default Sum Multiple Criteria

I'm trying to sum with multiple criteria. I've been reading other postings
and tried the following with no success:

=SUMPRODUCT(--('BOM LIST'!$J$8:$J$770=B31),--('BOM
LIST'!$A$8:$A$770={"S","S-FED","O"}),--('BOM
LIST'!$G$8:$G$770={"F","Q","R"}),'BOM LIST'!$Z$8:$Z$770)

Help!

Also, can anyone recommend a book to learn such advanced formulas?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Sum Multiple Criteria

Try

=SUMPRODUCT(('BOM LIST'!$J$8:$J$770=B31)*
('BOM LIST'!$A$8:$A$770={"S","S-FED","O"})*
('BOM LIST'!$G$8:$G$770={"F","Q","R"}),'BOM LIST'!$Z$8:$Z$770)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rhiannons_Wish" wrote in message
...
I'm trying to sum with multiple criteria. I've been reading other

postings
and tried the following with no success:

=SUMPRODUCT(--('BOM LIST'!$J$8:$J$770=B31),--('BOM
LIST'!$A$8:$A$770={"S","S-FED","O"}),--('BOM
LIST'!$G$8:$G$770={"F","Q","R"}),'BOM LIST'!$Z$8:$Z$770)

Help!

Also, can anyone recommend a book to learn such advanced formulas?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default Sum Multiple Criteria

Your problem is most likely due to using equality with an array when
you really want inclusion in a set. Although I have seen this construct
working, I only recently found out about it here in the newsgroups and
I have not yet clarified to myself when it works and when not.

Thus, the "natural" thought that this construct might work,
=IF(A2={"A", "B", "C"}, 1, 0)
implying that we would get 1 if A2 is either "A", "B" or "C",
does not work and needs instead and OR() as in:
=IF(OR(A2="A", A2="B", A2="C"), 1, 0)

Problem is, SUMPRODUCT() accepts computed arrays in some forms, like
in:
=SUMPRODUCT(A1:A10, --(B1:B10="A")),
in which case the second array argument is a computed array of TRUE or
FALSE based on whether Bi="A" for each i in 1..10.

BUT, at least in my version, it will not accept the following:
=SUMPRODUCT(A1:A10, IF(B1:B10="A", 1, 0)),
unless it is array-entered, i.e.entered with Shift+Ctrl+Enter. In this
case we force the second argument to be treated as an array to IF() and
thus producing a computed array of 1 and 0 based on the same condition.

In most cases, the benefit of SUMPRODUCT() is that it does not require
array-entering. However, in this case this benefit is defeated since we
need array entering anyway. Given this, it would likely be simpler to
use array-SUM() instead, like in the following formula, equivalent to
the last SUMPRODUCT. Notice that essentially we replace the ","
delimiter in SUMPRODUCT with the multiplication opeerator "*", since we
are summing over a computed array, itself the pairwise product of two
arrays
=SUM(A1:A10 * IF(B1:B10="A", 1, 0))

In conclusion, regarding your specific formula:
- Replace the ={...} construct with an IF(OR(...), 1, 0)
- Array enter your modified formula.

Optionally, you might use SUM(array * array * ...), which must also be
array-entered.

HTH
Kostis Vezerides

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default Sum Multiple Criteria

Hi Bob,

I was writing my own reply while you posted your answer. As I say in my
post, I have still not fully understood when ={...} works. In the post
I reflect my current understanding of this.

Can you please explain why your formula works? I tested it in my own
test data set and verified that its philosophy works. One thing I have
come to conclude myself since I wrote the post is that SUMPRODUCT,
without array entering, will accept as arguments computed arrays if
they are the result of operations. If however, the computed array is
the result of a function, then it needs array entering.

Yet, I am still puzzled by some things:

- In a column with values in {"A", "B", "C"} the following formula does
not work:
=IF(J3={"A","B"}, 1, 0)

If I simply enter it, then it produces #VALUE!.
If I array-enter it, it only recognizes the "A", consistent with the
behavior when an array is used in a formula, in a place where a scalar
is expected.
However, it obviously works in the following, same philosophy as your
formula, i.e. without array-entering:
=SUMPRODUCT(K2:K15*(J2:J15={"A","B"}))

This I cannot explain. Can you enlighten please?

Regards,
Kostis Vezerides

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
Multiple Criteria in SumProduct, N/A Result dcd123 Excel Worksheet Functions 7 October 7th 05 01:26 PM
Counting by multiple criteria Risky Dave Excel Worksheet Functions 4 September 28th 05 01:29 PM
Sum Multiple Criteria or DcountA rjenkins Excel Worksheet Functions 3 July 16th 05 12:21 AM
Multiple Criteria IF Nesting BethB Excel Worksheet Functions 2 May 17th 05 12:14 AM
Can I get the mode, min, and max with multiple criteria? BobT Excel Discussion (Misc queries) 1 February 15th 05 04:20 AM


All times are GMT +1. The time now is 11:05 PM.

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

About Us

"It's about Microsoft Excel"