2

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)

player0
  • 124,011
  • 12
  • 67
  • 124
xpzn
  • 23
  • 3
  • 1
    PSA: Do NOT share [spreadsheets](//meta.stackoverflow.com/a/260455)/[images](//meta.stackoverflow.com/q/285551) as the only source of data, to avoid closure of the question. Make sure to add input and expected output as **plain text table** to the question. [Click here](//webapps.stackexchange.com/a/161855) to create a table easily, which are **easier to copy/paste as well**. Also, note that [your email address can also be accessed by the public](//meta.stackoverflow.com/q/394304), if you share Google files. – TheMaster Nov 02 '22 at 14:06
  • 1
    That is extremely good to know, thank you. I've made a note of that! @TheMaster – xpzn Nov 02 '22 at 14:37

1 Answers1

1

try:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124