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!

 

[Aladin Akyurek]

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.

Late? Does that mean you resolved the issue you have?

Trolled? No.

 

[bill]

tactps, what does the '--' represent? Does it relate to OR?

 

[tactps]

No it coverts the array to 1 (meaning true) or 0 (meaning false)

Paste my formula into your sheet, and then use the function wizard. Remove the "-" and see what the array changes to - you'll get the idea.

 

[tactps]

Just to explain further:

if you use "--" it counts the number of cells that match the criteria.
If you don't it lists the values that match the criteria.

 

[bill]

To imply that any part of my being 'sucks' is to be trolled.
And yes I do have another solution for which I'll process the results row by row if SUMPRODUCT does not pan out.

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.

Late? Does that mean you resolved the issue you have?

Trolled? No.

 

[bill]

Just to explain further:

if you use "--" it counts the number of cells that match the criteria.
If you don't it lists the values that match the criteria.

=SUMPRODUCT(--(Product="123"),--(DeliveryMethod="456"),--(Company="789"),--(Type<>"1314"))
So how do you implement an OR in this equasion?
Far as I can tell, all array are of a type AND...

 

[Aladin Akyurek]

Just to explain further:

if you use "--" it counts the number of cells that match the criteria.
If you don't it lists the values that match the criteria.

=SUMPRODUCT(--(Product="123"),--(DeliveryMethod="456"),--(Company="789"),--(Type<>"1314"))
So how do you implement an OR in this equasion?
Far as I can tell, all array are of a type AND...

AND is generally speaking mapped on * in array and SumProduct formulas and comma in SumProduct formulas that invoke this function's native syntax.

OR can be mapped in many ways: + or by invoking IsNumber/Match idiom. Sometimes by means of a constant array.

 

[bill]

Aladin,
Thanks for the reply

For the following, how would you code Product can be either 123 or 456?

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

 

[Aladin Akyurek]

Aladin,
Thanks for the reply

For the following, how would you code Product can be either 123 or 456?

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

Are the Product, DeliveryMethod, Company, and Type numbers true numbers or text-formatted numbers? If the former, they should not be put between double quotes. I'll assume that they are true numbers...

Since you state either 123 or 456, an OR mapped onto + would suffice:

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

If the list of the product numbers to test against is big, it's better to switch to the IsNumber/Match idiom.

BTW, if your ranges are huge and the conditional terms in the formula (the forgoing formula has 5 of them) are too many, you'll suffer a noticeable performance degradation. That's in the nature of such formulas.

 

[PaddyD]

any reason you're not using a pivot table?