SUMPRODUCT Error
Good afternoon,
I have the formula ... =SUMPRODUCT(COUNTIF(E10:J10,N10:S10)) .... which I have copied down in column "T" some 2,500 cells which should always return the value 6. Unfortunately the formula only works on about 80% of the formulas although I know for a fact that the two sets of numbers match EXACTLY and are values as opposed to text etc. The only difference with the two sets of numbers to match is the first six are not sorted numerically where as the second set of numbers are. Thanks in advance, Paul |
SUMPRODUCT Error
Hi Paul
Try it this way: =SUMPRODUCT(--($E$10:$J$100),--($N$10:$S$10)) It equals 6 Regardless of which group be it ("E:J") or ("N:S") if any of these cells has a (0) zero, the equation will then display whatever the range has that meets the criteria. HTH Mick. |
SUMPRODUCT Error
I forgot to include a Text Equivalent....D'oh....
=SUMPRODUCT(--($E$10:$J$10<""),--($N$10:$S$10)) Again, if all the cells meet the criteria it will count up to 6, conversely, if any cells are blank within the range, the total will change to reflect.... HTH Mick. |
All times are GMT +1. The time now is 04:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com