Use SUMPRODUCT for a matrix

[Learn-more]

The case is I want to key in two criteria and the function give me back the
total. For example, I want Sun in the rows and Two in the column, then will
give me the total of 2 + 8 = 10. The Sun and the Two is input some where and
will be change from time to time.
A B C D
1 One Two Three
2 Sun 1 2 3
3 Mon 4 5 6
4 Sun 7 8 9

I tried using SUMPRODUCT but did not seem to work, may be due to my
knowledge is not good enough, anyone know how or have other functions that
make it work?

Thanks very much.

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((A2:A4="Sun")*(B11="Two")*(B24))

To make it more flexible it would be better to use cell references for the
criteria

=SUMPRODUCT((A2:A4=E1)*(B11=E2)*(B24))


Mike

 

[Learn-more]

Hi Mike,

That works, thank you.

Hi,

Try this

=SUMPRODUCT((A2:A4="Sun")*(B11="Two")*(B24))

To make it more flexible it would be better to use cell references for the
criteria

=SUMPRODUCT((A2:A4=E1)*(B11=E2)*(B24))


Mike

 

[Mike H]

Your welcome and thanks for the feedback

Hi Mike,

That works, thank you.