SUMProduct with certain text string as a criteria

[Edgie]

Hi,

I have trying to do a sumproduct formula but to exclude certain cells that have certain text in them,

my example is as follows


Buy/Sell Volume Price Reference

Buy 100 50 MM1
Buy 200 40 MMA1
Sell 40 50 MM2
Buy 20 20 MMA2


So for this i want to work out the value of all buys but exclude a reference that contains "MMA"

I can do the normal sumproduct for this but not to exclude the reference...

=SUMPRODUCT((C3:C6*E3:E6)*(A3:A6="Buy")) so that times the volume by the price for all buys, is there anyway to exlcude the ones that contain "MA" in the reference cell?

Also is there a way to include certain text but also to exclude certain text as well? So if example there were references other then MMA and MM so i would want to include ones with MM but exclude MMA for example?

Thanks

[TMS]

Maybe:

=SUMPRODUCT((C3:C6*E3:E6)*(A3:A6="Buy")*(F3:F6<>"*MA*"))


Regards, TMS

[zbor]

Here, try this:

=SUMPRODUCT(--(A3:A10="Buy"), E3:E10, C3:C10, --ISNUMBER(FIND("MA",F3:F10)))

[Edgie]

Thanks very much for your help guys!!

Zbor, i used your version because unfortunately TMS version used all texts that contained MM -

Zbor, is there anyway i can include text strings and but then refine it to exclude certain text strings say if text is very similar?

[zbor]

We would need more info about this...

Upload a workbook with several examples so we can find logic of similar

[Edgie]

Thanks Zbor,

Example of the data attached.

So for this example i want to total the value (volume x price) of all items that have a date of 01/01/2012, a buy, and has the reference AAA but doesn't have DD in the reference....notice how similar the references are and there are multiple entries that contain AAA and DD, i so i want to include the references AAA but exclude the ones that have DD.

Incase my upload doesn't work here is the data.

Buy/Sell Volume Price Date Reference

Buy 100 50 01/01/2012 AAA123456789DD
Buy 200 40 01/01/2012 Value of AAA 01-01-2012
Sell 40 50 01/02/2012 AAA12345678910BCD
Buy 20 20 01/01/2012 AAA12345612MNE

Thanks for your help

[Edgie]

Forgot to mention that there would be references that wouldn't include AAA in there so a simple exclude "DD" wouldn't help, it almost would have to include AAA but then exclude ones that have DD in there....does that make sense?

So if we just excluded "DD" then it would include stuff that doesn't have AAA in there

[zbor]

Maybe something like this:
(You can change criterias in H column)