ARRAY functions... Please don't say it's true!

[bill]

Had a visit to the following: http://www.j-walk.com/ss/excel/tips/tip74.htm and found a neat article regards using boolean tests in CTRL+SHIFT+ENTER functions BUT, they don't appear to work quite as I planned!

Populate cells a1:a6 with various letters and column b1:b6 with various values

Doing something like:=SUM((OR(A1:A6="b"))*(B1:B6)) will result in the a straight sum of ALL values in column B!

Help!!!!!! Yes, use SUMIF but SUMIF doesn't work good when the criteris is complex!

 

[tactps]

Its true (lol).
Try:
=SUMPRODUCT(--(A1:A6="b"),(B1:B6))
which is great for complex formulas.

Also, in your example, you don't need the OR statement:

=SUM((A1:A6="b")*(B1:B6))
will also give the correct result (enter with Shift-Ctrl-Enter)

 

[bill]

Thanks for the reply.

Yes, the example ad but one criteria.
The REAL formula does like this:
=SUM((Product="123")*(DeliveryMethod="456")*OR(Company="789",Company="101112")*(PhysicalType<>"1314")*(Product <> "1516")*(Product <> "1718")*AND(NOT((Company = "1920")*(Counterparty = "2122")*OR(LocationTarget = "2324", LocationTarget = "2526")),NOT((Company = "2728")*(Counterparty = "2930")*(Trade="3132")))*OFFSET(pYTDGross,0,F$92))

So you can see, I'm pretty much pooched!!!!
I believe eve the AND's are hosed!

 

[tactps]

Post your worksheet and explain what you want to do.

 

[bill]

In in lieu of a massive worksheet, here's the carrots (trying to cut back on beef).

All of the ranges specified in the query are from one massive table.
The only piece of data that is being returned comes from the last piece, pYTDGross. All the others are used for testing.

The idea is, on a row by row basis, to examine each record and if all of the criteria are correct, return the number.

The number is then summed.

 

[bill]

By the way, the SUMPRODUCT seems to work just like an ARRAY function.
The cavaet for ARRAY is appling functions within the array which unfortunately do not resolve on a row by row basis. They instead seem to resolve over the entire range they're target against and once resolved, are returned to the rest of the formula for processing.

That's why the OR provided unexpected results.
It first processed itse;f against the range specified and because it found @ least one value as TRUE, it returned a true to the rest of the function.

Very much suck

 

[Aladin Akyurek]

Thanks for the reply.

Yes, the example ad but one criteria.
The REAL formula does like this:
=SUM((Product="123")*(DeliveryMethod="456")*OR(Company="789",Company="101112")*(PhysicalType<>"1314")*(Product <> "1516")*(Product <> "1718")*AND(NOT((Company = "1920")*(Counterparty = "2122")*OR(LocationTarget = "2324", LocationTarget = "2526")),NOT((Company = "2728")*(Counterparty = "2930")*(Trade="3132")))*OFFSET(pYTDGrossTotalUSD,0,F$92))

You can't use OR/AND if the intention/the calculation must returm an array, not a scalar.

So you can see, I'm pretty much pooched!!!!

No wonder. Instead of forwarding a formula, you could describe the task that you want to accomplish.

There are a huge number of questions to pose judging from the not-well formed formula: Why do you put all those numbers - product numbers, company numbers, etc. between double quotes? Aren't they true numbers? Why that OFFSET bit - Are the ranges (Company, Counterparty, ..., pYTDGrossTotalUSD not of equal size?

I believe eve the AND's are hosed!

See above about OR's and AND's?

 

[Aladin Akyurek]

By the way, the SUMPRODUCT seems to work just like an ARRAY function.
The cavaet for ARRAY is appling functions within the array which unfortunately do not resolve on a row by row basis. They instead seem to resolve over the entire range they're target against and once resolved, are returned to the rest of the formula for processing.

That's why the OR provided unexpected results.
It first processed itse;f against the range specified and because it found @ least one value as TRUE, it returned a true to the rest of the function.

Very much suck

I'm sorry but it's your understanding of such things that "sucks". Since you're curious/inquisitive about formulas, I'm sure you'll get them to know...

 

[bill]

Aladin, although your response is late... thanks anyway.

BTW, I didn't post to be trolled.

My knowledge is well and dandy and I'll remind you that I resolved the answer in solitude.

 

[tactps]

Use sumproduct. A basic formula using named ranges is below:

=SUMPRODUCT(--(Product="123"),--(DeliveryMethod="456"),--(Company="789"),--(Type<>"1314"))