How does SUMPRODUCT() work for Multiple Criteria Aggregations?

[SkipVought]

Please check out FAQ68-7898.

Does it adequately explain how this function works?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bluedragon2]

Didn't like mine

Looks good...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Thanx! Never realized you had one.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

Your FAQs cover situations where ALL the tests need to be simultaneously satisfied (known in set theory as intersection, and in Boolean algebra as the AND'ing of conditions).[ ] This is achieved by "multiplying" the logical tests:
[ ] [ ] [ ](test 1) * (test 2) * (test 3) * ...

You can also accommodate situations where only ONE OR MORE of the tests need to be satisfied (known in set theory as union, and in Boolean algebra as the OR'ing of conditions).[ ] You can do this by "adding" the logical tests, then using the SIGN function to reduce to 1 any resulting number that is greater than 1.[ ] Thus:
[ ] [ ] [ ]SIGN( (test 1) + (test 2) + (test 3) + ... )

You can then build up even more complicated compound tests, with unions embedded in intersections and vice versa.

 

[SkipVought]

Well stated. But this FAQ is focused on a demonstration of how the multiple criteria works in a table/array of values, not an explanation of the ways to use it. It can be applied in any number of ways.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bluedragon2]

faq68-4725

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...