SUMPRODUCT Error

[Paul Black]

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

 

[BDeitrick]

Go to one of the cells that returns an error. In the formula bar for that cell, select the first range (the equivalent of E10:J10) and press F9, then do the same for the second range. Then copy the formula bar contents and post it here.

Bernie

 

[Vacuum Sealed]

Hi Paul

Try it this way:

=SUMPRODUCT(--($E$10:$J$10>0),--($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.

 

[Vacuum Sealed]

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.