SUMIF and {multiple criteria}

  • Thread starter Thread starter jamison.folland
  • Start date Start date
J

jamison.folland

Can anyone help me to understand why this formula gives the correct
result:
=(SUMPRODUCT((Region="Asia")*(DECONS="Ontime")*(SPLIT={"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
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
That makes very good sense, cheers! I strongly believe you are
correct
Click to expand...

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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)
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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>.)
 
Bob,

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



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
 
Back
Top