I have used the SUMPRODUCT feature before, but this time am getting a #VALUE
error and don't know why.

On Sheet2, Columns L and M, I have the following data (example):

(these are formulas extracting data from another cell)
Column L Column M
2 E
3 A
4 B
4 C


On Sheet1, I have the following setup:

1 (E6) 2 (F6) 3 (G6) 4
(H6)
A (D7) (Cell E7) (Cell F7) (Cell G7) (Cell H7)
B (D8) (Cell E8) (Cell F8) (Cell G8) (Cell H8)
C (D9) (Cell E9) (Cell F9) (Cell G9) (Cell H9)
D (D10) (Cell E10) (Cell F10) (Cell G10) (Cell H10)
E (D11) (Cell E11) (Cell F11) (Cell G11) (Cell H11)


I want to calculate/count in Cell E7 on Sheet1, how many times the number 1
and the letter A on Sheet2 (in columns L and M) appear (and so forth),
returning a "0" if there are none.

The current formula I am using is:
=SUMPRODUCT(--('Sheet2'!$L$2:$L$1000=E6),--('Sheet2'!$M$2:$M$1000=D7))

Thanks again for your great help.

Mike

Are there any #value errors in the L$2:$L$1000 or $M$2:$M$1000 range///
--
If this post helps click Yes
---------------
Jacob Skaria


"lawandgrace" wrote:

I have used the SUMPRODUCT feature before, but this time am getting a #VALUE
error and don't know why.

On Sheet2, Columns L and M, I have the following data (example):

(these are formulas extracting data from another cell)
Column L Column M
2 E
3 A
4 B
4 C


On Sheet1, I have the following setup:

1 (E6) 2 (F6) 3 (G6) 4
(H6)
A (D7) (Cell E7) (Cell F7) (Cell G7) (Cell H7)
B (D8) (Cell E8) (Cell F8) (Cell G8) (Cell H8)
C (D9) (Cell E9) (Cell F9) (Cell G9) (Cell H9)
D (D10) (Cell E10) (Cell F10) (Cell G10) (Cell H10)
E (D11) (Cell E11) (Cell F11) (Cell G11) (Cell H11)


I want to calculate/count in Cell E7 on Sheet1, how many times the number 1
and the letter A on Sheet2 (in columns L and M) appear (and so forth),
returning a "0" if there are none.

The current formula I am using is:
=SUMPRODUCT(--('Sheet2'!$L$2:$L$1000=E6),--('Sheet2'!$M$2:$M$1000=D7))

Thanks again for your great help.

Mike