SUMIF and {multiple criteria}
 

Can anyone help me to understand why this formula gives the correct
result:
=(SUMPRODUCT((Region="Asia")*(DECONS="Ontime")*(SP LIT={"E","W"}))
+SUMPRODUCT((Region="Asia")*(DECONS="Ontime from
ETA")*(SPLIT={"E","W"})))

But if I try to condense it further to:
=SUMPRODUCT((Region="Asia")*(DECONS={"Ontime from
ETA","Ontime"})*(SPLIT={"E","W"}))

It stops working?

Seems to me that, if it can manage to look for either "E" or "W" in a
single SUMPRODUCT, why can't it also look for "Ontime" or "Ontime from
ETA" in the same SUMPRODUCT?

Can SUMPRODUCT only handle one of these per array?

Thanks much,
Jamison

SUMIF and {multiple criteria}
 

I believe that reason is that it checks for DECONS="Ontime from ETA" and
SPLIT="E"

then

DECONS="Ontime" and SPLIT="W"

In other words the first item in DECONS and the first item in the SPLIT
array then the 2nd item in DECONS and the 2nd item in the SPLIT array. NOT
the first item in the DECONS array with both items in the SPLIT array.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


wrote in message
ups.com...
Can anyone help me to understand why this formula gives the correct
result:
=(SUMPRODUCT((Region="Asia")*(DECONS="Ontime")*(SP LIT={"E","W"}))
+SUMPRODUCT((Region="Asia")*(DECONS="Ontime from
ETA")*(SPLIT={"E","W"})))

But if I try to condense it further to:
=SUMPRODUCT((Region="Asia")*(DECONS={"Ontime from
ETA","Ontime"})*(SPLIT={"E","W"}))

It stops working?

Seems to me that, if it can manage to look for either "E" or "W" in a
single SUMPRODUCT, why can't it also look for "Ontime" or "Ontime from
ETA" in the same SUMPRODUCT?

Can SUMPRODUCT only handle one of these per array?

Thanks much,
Jamison

SUMIF and {multiple criteria}
 

That makes very good sense, cheers! I strongly believe you are
correct.

SUMIF and {multiple criteria}
 

wrote in message
ps.com...
That makes very good sense, cheers! I strongly believe you are
correct

If not there will soemone along in a minute to put us both right <g

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

SUMIF and {multiple criteria}
 

You could try this

=SUMPRODUCT((Region="Asia")*(SIGN((DECONS="Ontime from
ETA")+(DECONS="Ontime")))*(SIGN((SPLIT="E")+(SPLIT ="W"))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ps.com...
That makes very good sense, cheers! I strongly believe you are
correct.

SUMIF and {multiple criteria}
 

Bob,

Does it need the SIGN() function calls? It seems to work for me without
them.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Bob Phillips" wrote in message
...
You could try this

=SUMPRODUCT((Region="Asia")*(SIGN((DECONS="Ontime from
ETA")+(DECONS="Ontime")))*(SIGN((SPLIT="E")+(SPLIT ="W"))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



wrote in message
ps.com...
That makes very good sense, cheers! I strongly believe you are
correct.

SUMIF and {multiple criteria}
 

Since Decons is a single column, it will contain one or the other (or neither).
So the sum will be 1+0 or 0+1 or 0+0. So the =SIGN() function isn't required.

I bet Bob <bg was thinking that by including the =SIGN() function in the
formula, it would be easier for the OP to change the formula to look at two
columns (decons1, decons2, and split1, split2:

=SUMPRODUCT((Region="Asia")*
(SIGN((DECONS1="Ontime from ETA")+(DECONS2="Ontime")))
*(SIGN((SPLIT1="E")+(SPLIT2="W"))))

Then you wouldn't have to worry about both values occuring at the same time:
0+0
1+0
0+1
or
1+1

The =SIGN() function will make sure that the non-zero sum will be treated as 1.

(or Bob could have just screwed up when he tried to make Harlan happy <gdr.)



Sandy Mann wrote:

Bob,

Does it need the SIGN() function calls? It seems to work for me without
them.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Bob Phillips" wrote in message
...
You could try this

=SUMPRODUCT((Region="Asia")*(SIGN((DECONS="Ontime from
ETA")+(DECONS="Ontime")))*(SIGN((SPLIT="E")+(SPLIT ="W"))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



wrote in message
ps.com...
That makes very good sense, cheers! I strongly believe you are
correct.





--

Dave Peterson

SUMIF and {multiple criteria}
 

On Aug 29, 5:25 pm, "Sandy Mann" wrote:
Bob,

Does it need the SIGN() function calls? It seems to work for me without
them.



Bob,

Yes, it works! I guess you need both SIGN() for both variables to
make it all come together. I don't fully understand why, but it
worked, and that's the crux really. :)

Cheers both,
Jamison