CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Jezun
Jezun

Reputation: 77

How to (SUM(IF(REGEXMATCH(IMPORTRANGE with multiple criteria/conditions?

My formula below won't have any error, however, it's not providing me the right value. I am trying to get the total number of products with a project name: regular campaign, and a status of Disco/Supplier Status and On Hold. I only have Disco/Supplier status below because I can't even get it right how much more adding On Hold status.

=ArrayFormula(SUM(IF(REGEXMATCH(IMPORTRANGE("1eDIDiMfE5bIi-hEVtwJ7t2wD7A5zTRSt2mHhoQ_mUrQ/edit#gid=359656787","HIP Campaign!H:H"),"Regular Campaign*"),IMPORTRANGE("1eDIDiMfE5bIi-hEVtwJ7t2wD7A5zTRSt2mHhoQ_mUrQ/edit#gid=359656787","HIP Campaign!M:M"),"Disco/Supplier Status"),IMPORTRANGE("1eDIDiMfE5bIi-hEVtwJ7t2wD7A5zTRSt2mHhoQ_mUrQ/edit#gid=359656787","HIP Campaign!L:L"), 0))

Upvotes: 1

Views: 417

Answers (2)

MattKing
MattKing

Reputation: 7773

You might try this formula:

=QUERY({IMPORTRANGE("1eDIDiMfE5bIi-hEVtwJ7t2wD7A5zTRSt2mHhoQ_mUrQ","HIP Campaign!H4:M")},"select SUM(Col5) where where Col1 contains 'Regular Campaig' and Col6 matches 'Disco/Supplier|On Hold' label SUM(Col5)''")

Upvotes: 0

player0
player0

Reputation: 1

try like this:

=ARRAYFORMULA(SUM(IF(
 (REGEXMATCH(IMPORTRANGE("1eDIDiMfE5bIi-hEVtwJ7t2wD7A5zTRSt2mHhoQ_mUrQ", "HIP Campaign!H4:H"),
 "Regular Campaig.*"))*
 (REGEXMATCH(IMPORTRANGE("1eDIDiMfE5bIi-hEVtwJ7t2wD7A5zTRSt2mHhoQ_mUrQ", "HIP Campaign!M4:M"),
 "Disco/Supplier|On Hold")),
             IMPORTRANGE("1eDIDiMfE5bIi-hEVtwJ7t2wD7A5zTRSt2mHhoQ_mUrQ", "HIP Campaign!L4:L"), 0))

Upvotes: 2

Related Questions