string matching

  • Thread starter Thread starter adimar
  • Start date Start date
A

adimar

Please help me understand why these apparently equivalent constructs behave
differently? I use them as multipliers in SUMPRODUCT.

The formula is something like:
=SUMPRODUCT((Product={"MGC","EMS"})*(Type={"Service Issue","Upgrade"}))


This one does not work as expected: (Type={"Service Issue","Upgrade"})

This one does work as expected: ((Type="Service Issue") + (Type ="Upgrade"))

I expect the formula to count records of Type "Service Issue" OR "Upgrade",
that is allow for either.


Thank you,
Marta
 
Try like this:

=SUMPRODUCT((ISNUMBER(MATCH(Product,{"MGC","EMS"},0)))*(ISNUMBER(MATCH(Type,{"Service Issue","Upgrade"},0))))
 
Yes, the formula you proposed works.

The question still remains: What is wrong with this syntax?
(Type={"Service Issue","Upgrade"})

Why does this one work?
(Product={"MGC","EMS"})

I would prefer this syntax as it is more compact and makes troubleshooting
or review easier.


Thank you.
 
Back
Top