Identify row in array for COUNTIF

[dreid1011]

In relation to another problem I am working on, how might I identify a variable row within an array based on the location of the current cell being processed in a second array?

I have this range which I would like to count all the values of "B" in a row dependent on another range (below):

Book1 5-31-2024.xlsx
	A	B	C	D	E
4				G
5	G			B
6		B			G
7	B
8			G		B
9		G	B
Sheet1

This range has the cell I want to use to identify the row to count above for values of "B". The cell with 10 determines which row I would like to count above:

Book1 5-31-2024.xlsx
	P	Q	R	S	T
18	0	0	0	0	0
19	0	0	0	0	0
20	0	0	0	0	10
21	0	0	0	0	0
22	0	0	0	0	0
23	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
P18:T23	P18	=MAP(MAP($A$4:$E$9,LAMBDA(a,IF(a="G",1,0)))*MAP($M$4:$M$9,LAMBDA(a,IF(a="T",10,0))),LAMBDA(b,b))
Dynamic array formulas.

Ignore the outer MAP( ... ,LAMBDA(b,b)), that is where I am trying to integrate the count of "B" values.

I already have this BYROW to count the values of "B" per row, but I can't figure out how to integrate that into the second array above without getting errors:

Book1 5-31-2024.xlsx
	N
18	0
19	2
20	2
21	2
22	2
23	2
Sheet1

Cell Formulas
Range	Formula
N18:N23	N18	=BYROW(A4:E9,LAMBDA(row,COUNTIF(row,"B")*2))
Dynamic array formulas.

The goal is to use the cell that contains the 10 in the second table to identify the corresponding row in the first table and count the values of "B" and add that count * 2 to the value of 10. In the example, it would count 1 "B" in the third row and add 2 (1*2) to the 10 in the second table for a total of 12. The result should look like the second array but with 12 instead of 10.

 

[hagia_sofia]

Something like:

=MAP(P18:T23,(BYROW((A4:E9="B")*2,LAMBDA(a,SUM(a))))*(P18:T23<>0),LAMBDA(a,b,a+b))

?

 

[Cubist]

How about...

Book2
	A	B	C	D	E
1
2
3
4				G
5	G			B
6		B			G
7	B
8			G		B
9		G	B
10
11
12	0	0	0	0	0
13	0	0	0	0	0
14	0	0	0	0	10
15	0	0	0	0	0
16	0	0	0	0	0
17	0	0	0	0	0
18
19
20	0	0	0	0	0
21	0	0	0	0	0
22	0	0	0	0	12
23	0	0	0	0	0
24	0	0	0	0	0
25	0	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
A20:E25	A20	=IF(A12:E17=10,A12:E17+BYROW(A4:E9,LAMBDA(b,COUNTIF(b,"B")))*2,0)
Dynamic array formulas.

 

[dreid1011]

Something like:

=MAP(P18:T23,(BYROW((A4:E9="B")*2,LAMBDA(a,SUM(a))))*(P18:T23<>0),LAMBDA(a,b,a+b))

?

=IF(A12:E17=10,A12:E17+BYROW(A4:E9,LAMBDA(b,COUNTIF(b,"B")))*2,0)

Both of those are brilliant, thank you! Just goes to show how I was trying to overthink things. I'd mark them both if I could. I'll use Cubist's approach as it's a little shorter though.