SUMPRODUCT Error

P

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
 
V

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.
 
V

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top