counting multiple criteria using SUMPRODUCT and getting #VALUE err
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 |
counting multiple criteria using SUMPRODUCT and getting #VALUE err
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 |
All times are GMT +1. The time now is 08:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com