Use SUMPRODUCT for a matrix
 

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.

Use SUMPRODUCT for a matrix
 

Hi,

Try this

=SUMPRODUCT((A2:A4="Sun")*(B1:D1="Two")*(B2:D4))

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

=SUMPRODUCT((A2:A4=E1)*(B1:D1=E2)*(B2:D4))


Mike

"Learn-more" wrote:

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.

Use SUMPRODUCT for a matrix
 

Hi Mike,

That works, thank you.


"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((A2:A4="Sun")*(B1:D1="Two")*(B2:D4))

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

=SUMPRODUCT((A2:A4=E1)*(B1:D1=E2)*(B2:D4))


Mike

"Learn-more" wrote:

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.

Use SUMPRODUCT for a matrix
 

Your welcome and thanks for the feedback

"Learn-more" wrote:

Hi Mike,

That works, thank you.


"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((A2:A4="Sun")*(B1:D1="Two")*(B2:D4))

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

=SUMPRODUCT((A2:A4=E1)*(B1:D1=E2)*(B2:D4))


Mike

"Learn-more" wrote:

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.