The goal is to create an arrayformula that looks over two separate columns and returns a SUM if it matches a certain string.

Here's an example table:

The first thing I wanted to pull, was the total number of Data base entries, regardless of the spelling. Which works

For that I used:

=ArrayFormula(Sum(CountIfs(A2:A8, {"db","data b*","database"})))

On that note: I know that's not scalable to keep adding different string variations, it's a one-off-scenario.

What I'd like to return is "For all Database entries, return the SUM where status = Completed". Which would be 0 in this scenario.

I tried adding another arrayformula into the above but I'm not sure how to reference only those items found in the previous formula? If that makes sense?

To visualise the confusing explanation:

=ArrayFormula(Sum(CountIfs(A2:A8, {"db","data b*","database"}) AND "WHERE STATUS IS COMPLETE"))

Could someone point me into the right direction? I'm happy to read through any documentation (only started looking at excel formulas today for the first time)

CodePudding user response：

try:

=SUMPRODUCT(B:B="completed", REGEXMATCH(A:A, "(?i)database|db|data b"))