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:
| Feature | Status | Description |
|---|---|---|
| API | Completed | Lorem ipsum |
| Database | In review | lorem ipsum |
| Server | Backlog | lorem ipsum |
| Load Balancer | Completed | lorem ipsum |
| DB | QA | lorem ipsum |
| LB | Completed | lorem ipsum |
| Data base | Backlog | lorem ipsum |
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)
>Solution :
try:
=SUMPRODUCT(B:B="completed", REGEXMATCH(A:A, "(?i)database|db|data b"))
