SUMPRODUCT with OR function

[The_Rock]

Hi Guys
I've got a sumproduct formula, and saw from another post how to add in an OR function.
Its working but what I need it to do is to exclude if it meets the OR function:

=SUMPRODUCT((rABReseller="Non-AB Reseller")*(EPP_Rev_Strm="New Lic")*(EPP_PA="PA")*(ISNUMBER(MATCH(N10:N54,{"Unassigned","To Be Assigned"})))*EM_PP)

So in the above, if it is "Unassigned" or "To Be Assigned", it should be excluded from the calculation.

Ta

 

[Norie]

What OR function?

I don't see one in the formula you've posted.

 

[The_Rock]

Ah, fair point.
The OR bit would be:

(ISNUMBER(MATCH(N10:N54,{"Unassigned","To Be Assigned"}

from the thread:
http://www.mrexcel.com/forum/showthread.php?t=192887

 

[Norie]

I suppose you could say that's a sort of OR but not quite.

I think the best thing might be to tell us what you want to do in words.

You've posted rather a complicated, for me anyway, formula but not much explanation.

 

[The_Rock]

No worries, basically, I want to total the revenue based on 4 criteria.

Criteria 1 is where the range: rABReseller="Non-AB Reseller"
Criteria 2 is where the range: EPP_Rev_Strm="New Lic"
Criteria 3 is where the range: EPP_PA="PA"
Criteria 4 is where the range: N10:N54 <> "Unassigned" OR "To Be Assigned"

Sums up the values in the range: EM_PP

Hope that helps.

 

[Hoggle]

Do the {} mean not contained in? When testing this formula with my data it seems that it counts any instance of the text within the brackets...

 

[The_Rock]

Exactly, at the moment it counts that text. I want it to exclude that text from the calculation.

I'm not sure what it means. I've posted a link in the email history from where I got the {}.

 

[Hoggle]

You forgot to put the ,0 fomula should read =SUMPRODUCT((rABReseller="Non-AB Reseller")*(EPP_Rev_Strm="New Lic")*(EPP_PA="PA")*(ISNUMBER(MATCH(N10:N54,{"Unassigned","To Be Assigned"},0)))*EM_PP)

 

[The_Rock]

I presume you're refering to the link - I know thats what he wanted. I want to modify my formula to exclude based on an 'OR' function.
The formula I used was for guidance. If it can be re-written another way, please amend as necessary.

Thanks

 

[Hoggle]

Sorry I'm just learning, seems I need to go away and try to learn some more.....