3

I have a problem with an OR logic in a query. I'm trying to make a sum of a column (D), if:

• column A = cars/build-price/carselector.html

• column B = contains ateca or ibiza (the cell could be "get the ateca", "new ibiza xx", ...)

• column C = footer

First tried with a SUMIFS but didn't work:

=SUMIFS(C:C;A:A;"cars/build-price/carselector.html";B:B;{"ateca"; "ibiza"};D:D;"footer")

then tried with a QUERY, but I don't really know this language:

=SUM(QUERY(A:D;"Select C where A='cars/build-price/carselector.html' and D='footer' and (B='ateca' or B='ibiza')"))

Can't find how to fit the "contains" and the "or" logic.

Someone can help?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Guillaume Hubert
  • 33
  • 1
  • 1
  • 5

2 Answers2

4

A query could be:

=QUERY(A:D,"select sum(D) where A='cars/build-price/carselector.html' and C='footer' and (B contains 'ateca' or B contains 'ibiza')")
pnuts
  • 58,317
  • 11
  • 87
  • 139
1

Try this formula:

=SUMPRODUCT(C:C,ArrayFormula((A:A="cars/build-price/carselector.html")*(D:D="footer")*((N(REGEXMATCH(B:B,"ateca")))+(N(REGEXMATCH(B:B,"ibiza"))))))

Chris Hick
  • 3,004
  • 1
  • 13
  • 15