sumproduct doesn't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using this formula =sumproduct(--(G33:G37="OTT"),N33:N37) this should sum
values of N33 throug N37 if the corresponding G33 through G37 has OTT in the
cell. It returns 0. If I change the contents of cells G33:G37 from OTT to
another group of letters such as dl and update the formula it returns the
correct response. What could possibly be wrong with using OTT in the cell
that it won't work in the formula - very strange.
 
Thanks Gary but no - no space in OTT. The column contains many different
acronyms (not just OTT) and none of them work until I change the acronym to
something else, alter the formula to reflect that change. Once again - very
strange!
 
Not sure why your formula doesn't work but you only really need SUMIF

=sumif(G33:G37,"OTT",N33:N37)
 
Is Calculation set to Manual?

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Do you have (oh) T T or (zero) T T in the cell?

And does it match what you have in the formula?
 
No - if I change the cell to any other word it works fine - leads me to
believe the contents of the cells are somehow linked to another function that
won't allow their inclusion in a formula or somehow renders them
unrecognizable. I'm working with someone else's spreadsheet so I'm not
totally familiar with what has been done.
 
I tried this as well after struggling with sumproduct and coudn't get it to
work either. Thanks.
 
Hi Dave,

Capital O as in Oh

I've tried this on a new spreadsheet and it works just fine - something on
the existing sheet won't allow any of the acronyms to be seen in a new
formula. I've turned off 'Autosum' and 'Subtotals' to see if these affected
it but no change. I can put these acronyms in another column and alter the
formula and they work.
 
Fixed it - When I was typing in the acronym into the cell the autotyping was
finishing it off with the additional spaces the other cells contained -
that's a bit embarrassing! Thanks for everyone's help here and sorry for the
mind bender.
 
Back
Top