D
David
I have a spreadsheet with criteria in the first column
(for about 100 rows) starting with A2 and criteria in the
first row (about 100 columns) starting in B1, with
numbers that corrrespond to the values combinations.
I would like to write a formula that finds the
corresponding row and column criteria. For example, for
the following simplified case:
A B C D E A F Z D E F
A1 2 4 6 8 10 12 14 16 18 20 22
BB 3 6 9 12 15 18 21 24 27 30 33
A1 1 2 3 4 5 6 7 8 9 10 11
I would like to find the sum of the numbers where the
value is D and A1. (In this example, the solution is 39)
I wanted to use ={sum(if(b1:L1="d",if
(a2:a4="a1",b2:L12,0),0))}
However, I don't think I can use vertical and horizontal
arrays in the same formula (can I?). I am a rookie with
array formulas and would appreciate any help.
Thanks for your help!!
(for about 100 rows) starting with A2 and criteria in the
first row (about 100 columns) starting in B1, with
numbers that corrrespond to the values combinations.
I would like to write a formula that finds the
corresponding row and column criteria. For example, for
the following simplified case:
A B C D E A F Z D E F
A1 2 4 6 8 10 12 14 16 18 20 22
BB 3 6 9 12 15 18 21 24 27 30 33
A1 1 2 3 4 5 6 7 8 9 10 11
I would like to find the sum of the numbers where the
value is D and A1. (In this example, the solution is 39)
I wanted to use ={sum(if(b1:L1="d",if
(a2:a4="a1",b2:L12,0),0))}
However, I don't think I can use vertical and horizontal
arrays in the same formula (can I?). I am a rookie with
array formulas and would appreciate any help.
Thanks for your help!!