I have an spreadsheet like this:
| product name | sold? |
|---|---|
| banana | FALSE |
| banana | TRUE |
| apple | TRUE |
| apple | FALSE |
| apple | FALSE |
I’d like to add another column to display the number of unsold products. so the desired result would be something like this:
| product name | sold? | available |
|---|---|---|
| banana | FALSE | 1 |
| banana | TRUE | 1 |
| apple | TRUE | 2 |
| apple | FALSE | 2 |
| apple | FALSE | 2 |
Thanks.
>Solution :
You need COUNTIFS(). Try-
=COUNTIFS($A$2:$A,A2,$B$2:$B,FALSE)
For dynamic spill result, use MAP() function.
=MAP(A2:A6,LAMBDA(x,COUNTIFS(A:A,x,B:B,FALSE)))
To refer full column as input, use-
=MAP(TOCOL(A2:A,1),LAMBDA(x,COUNTIFS(A:A,x,B:B,FALSE)))
