SUMIF with wildcard, need to exclude a criteria

I am grouping web search results concepts and then counting how many times the concept was searched.
For example, we grouped together people searching for road conditions but they might have used various search terms like: "road condition", "traffic", "road closure" etc. My problem is that in another concept uses the search term "traffic counts". So for the Road Conditions I want to add when the search term "traffic" (with any additional combination of words) was used but not "traffic counts".

My current formula is such:
SUMIF(A:A,"*road condition*",B:B)+SUMIF(A:A,"*traffic*",B:B)+SUMIF(A:A,"*road closure*",B:B)+SUMIF(A:A,"*highway condition*",B:B)+SUMIF(A:A,"*road construction*",B:B)+SUMIF(A:A,"*highway closure*",B:B)+SUMIF(A:A,"*traffic map*",B:B)+SUMIF(A:A,"*road report*”,B:B)

How do I tell it not to sum instances of "traffic counts"?

Using Excel 2011 for Mac or 2010 on PC.

Thanks in advance
Donna

Why don't you just subtract that particular condition from what you already have, like this:

=your_existing_formula - SUMIF(A:A,"traffic counts",B:B)

Also, you don't really need "*traffic map*" as a condition because "*traffic*" will also count those, so you will get some double-counts.

Hope this helps.

Pete

Hi Donna
Welcome to the board

Since you have many conditions, it would maybe be better if you'd use 2 ranges in the worksheet to write the text to be included and excluded.
This would make the formula easier to read and to maintain.

For ex.:

Write "road condition","traffic","road closure","highway condition","road construction","highway closure","road report" in Y1:Y7
Write "traffic counts","traffic map" in Z1:Z2

Then use the formula

=SUMPRODUCT(SUMIF(A:A,"*"&Y1:Y7&"*",B:B))-SUMPRODUCT(SUMIF(A:A,"*"&Z1:Z2&"*",B:B))

Thank you both for your responses. I'm excited to learn more about excel once I graduate from getting my masters in December I plan to devote much of my new found free time to the task.

Pete, your response is so easy, I was trying to make it much more complicated. Should always remember to think about a simple solution.

Iecxe, I really like your response - I can see it making future changes or updates very easy.

Thank you both for your responses. Always more than one way to skin a cat, as they say.

Donna

Glad to be of help, Donna, and thanks for the rep. You can learn a lot about Excel by reading responses to other problems on the Forum.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete