Sumproduct Problem

  • Thread starter Thread starter JAgger1
  • Start date Start date
J

JAgger1

I'm using Sumprduct to get a count of matching numbers in the
following two sets, going from A1:T1, A2:T2

4 12 14 15 16 20 21 22 28 29 31 36 43 47 49 58 59 60 66 69

5 7 9 13 16 21 27 30 31 37 41 43 45 48 51 52 56 59 64 67

the formula I'm using is =SUMPRODUCT(--(COUNTIF(A1:T1,A2:T2)>0))

I keep getting an answer of 0, the answer should be 4 (16, 21, 31, 43
match)

Can anyone see what I'm doing wrong?? Thanks
 
Hi Jagger

Don't know why you're getting zero.

I replicated this and it worked fine for me, although the answer is
actually 5 ( 16, 21, 31, 43, 59)

Cheers
Mick
 
Hi Jagger

Don't know why you're getting zero.

I replicated  this and it worked fine for me, although the answer is
actually 5 ( 16, 21, 31, 43, 59)

Cheers
Mick
Click to expand...

Yup, answer should be 5

Still can't get the right answer??? I've deleted everything on the
spreadsheet, replaced all the numbers and still get 0???
 
Yup, answer should be 5

Still can't get the right answer??? I've deleted everything on the
spreadsheet, replaced all the numbers and still get 0???
Click to expand...

K, closed Excel, started a new worksheet and everything works fine??
Going for coffee....
 
K, closed Excel, started a new worksheet and everything works fine??
Going for coffee....
Click to expand...

Jagger

Could be that the sheet had it's auto calculation switch off.

Should you come across this in the future, hit F9 and see if it updates.

Cheers
Mick.
 
Back
Top