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

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

Well-known Member
Joined
Mar 7, 2002
Messages
550
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!
 

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550

ADVERTISEMENT

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

Well-known Member
Joined
Mar 7, 2002
Messages
550
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

bill said:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
bill said:
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

Well-known Member
Joined
Mar 7, 2002
Messages
550
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Use sumproduct. A basic formula using named ranges is below:

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

Forum statistics

Threads
1,147,696
Messages
5,742,679
Members
423,747
Latest member
Shadeslayers09

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top