Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct doesn't work
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct doesn't work
One possibility is that the cells in column G contain OTT followed by a space.
-- Gary''s Student "Bonkers" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct doesn't work
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! "Gary''s Student" wrote: One possibility is that the cells in column G contain OTT followed by a space. -- Gary''s Student "Bonkers" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct doesn't work
Not sure why your formula doesn't work but you only really need SUMIF =sumif(G33:G37,"OTT",N33:N37) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535217 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct doesn't work
Do you have (oh) T T or (zero) T T in the cell?
And does it match what you have in the formula? Bonkers wrote: 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. -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct doesn't work
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. "Sandy Mann" wrote: Is Calculation set to Manual? -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Bonkers" wrote in message ... 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! "Gary''s Student" wrote: One possibility is that the cells in column G contain OTT followed by a space. -- Gary''s Student "Bonkers" wrote: 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct doesn't work
I tried this as well after struggling with sumproduct and coudn't get it to
work either. Thanks. "daddylonglegs" wrote: Not sure why your formula doesn't work but you only really need SUMIF =sumif(G33:G37,"OTT",N33:N37) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535217 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct doesn't work
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. "Dave Peterson" wrote: Do you have (oh) T T or (zero) T T in the cell? And does it match what you have in the formula? Bonkers wrote: 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. -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct doesn't 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. "Dave Peterson" wrote: Do you have (oh) T T or (zero) T T in the cell? And does it match what you have in the formula? Bonkers wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does this Formula work? | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
DIV/0! error in SumProduct formula with no division | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions |