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

#### bill

##### Well-known Member
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### tactps

##### Well-known Member
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

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

##### Well-known Member
Post your worksheet and explain what you want to do.

#### bill

##### Well-known Member
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
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

##### MrExcel MVP
bill said:

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?

##### MrExcel MVP
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

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
Use sumproduct. A basic formula using named ranges is below:

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

Replies
23
Views
5K
Replies
6
Views
229
Replies
1
Views
3K
Replies
1
Views
432
Replies
3
Views
587

1,181,994
Messages
5,933,161
Members
436,883
Latest member
RyanI1986

### 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.

### Which adblocker are you using?

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

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