If contains ,MX

[RobB11]

I have a spreadsheet with several travel markets around the world and on the same line it contains revenue for the market. I need to SUM the value of all travel to Mexico but the only way it's displayed in the report is by Guanajuato, MX-Raleigh/Durham, NC or Detroit, MI-Guanajuato, MX the only common item is , MX and it's always in colum D with a cell range of D2:D900

How can I search out just , MX and have the revenue from cells E2:E9000 sum on another page?

[Cutter]

Welcome to the forum.
Upload a small sample file (with no personal data).

[johnjohns]

May be with sumproduct. See the attachment.

[zbor]

Better with SUMIF:

=SUMIF(DATA!D:D, "*"&A10&"*", DATA!E:E)

changin only DATA!E:E for other columns.

[RobB11]

zbor,

That worked when pulling, MX however I discovered that there is 1 additional piece I need.

It has to identify North America-North America first (that's where we dumb the Mexico info) then has to identify, MX so I can separate it form the rest of North America (we treat it differently).

I have attached a new example with the added info.

[Cutter]

So you want a total number of flights that have ", MX" in column D but do not have "North America-North America" in column B?

If so, in B5 use this:

Please Login or Register  to view this content.

[Roger Govier]

Hi

I would just add an extra column at the end of the data with a formula

=IF(ISNUMBER(FIND("MX",D2)),1,0)

Then turn the data into a List, by placing cursor within data and choosing Data>List>Create List

A simple Pivot Table will then produce all the information you want. (see attached)

[RobB11]

Sorry I want to find all of the lines that have both North America-North America in colum B and , MX in colum D. Then I need to be able to sum all of the flights in colum E, all in the same formula.

The above equations did not work.

[Cutter]

If you want to go with the formula solution just change the "<>" to "=" in the formula I gave you.

[RobB11]

Thank you that worked!

[Cutter]

You're welcome.
Don't forget to mark your thread as SOLVED (click FAQ at top of page for instructions).