Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct DIV/0 Error | Excel Worksheet Functions | |||
Sumproduct error | Excel Programming | |||
SUMPRODUCT #VALUE! Error | Excel Worksheet Functions | |||
SUMPRODUCT Error | Excel Programming | |||
SUMPRODUCT #N/A error | Excel Discussion (Misc queries) |