string matching
 

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={"Servic e 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

string matching
 

Try like this:

=SUMPRODUCT((ISNUMBER(MATCH(Product,{"MGC","EMS"}, 0)))*(ISNUMBER(MATCH(Type,{"Service Issue","Upgrade"},0))))



"adimar" wrote:

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={"Servic e 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

string matching
 

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.


"Teethless mama" wrote:

Try like this:

=SUMPRODUCT((ISNUMBER(MATCH(Product,{"MGC","EMS"}, 0)))*(ISNUMBER(MATCH(Type,{"Service Issue","Upgrade"},0))))



"adimar" wrote:

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={"Servic e 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