Excel Sum Product

1
votes

Currently in my excel spreadsheet i have this formula in one of my cell

=SUMPRODUCT(('CDT DWGS-2014'!G:G=2014)*
            ('CDT DWGS-2014'!S:S<>"")*
            ('CDT DWGS-2014'!Q:Q>='CDT DWGS-2014'!S:S)*
            ('CDT DWGS-2014'!S:S>=DATEVALUE("01-Jan-2014"))*
            ('CDT DWGS-2014'!S:S<DATEVALUE("01-Feb-2014"))
  )

i want to add this next line of code into the previous code but it keeps giving me a #value error.

*('CDT DWGS-2014'!T:T>1)*

is it because in the column T, i have both numbers and text in there? How would i go about getting the sum product value without changing the column T? I need both number and text in the column. In each cell in column T, it's either "Jon Not Finished" or negative or positive numbers

Thank you

1
excelexcel-2007excel-formulaexcel-2010
check whether your column T has any errors, e.g. using this formula: =SUMPRODUCT(1*(ISERROR('CDT DWGS-2014'!T:T)))? what it returns? – Dmitry Pavliv
your column contains error - most likely #VALUE - and this is the reason of error of your formula – Dmitry Pavliv
Thank you Simoco, you are awesome – HumanlyRespectable
Can you share your workbook (e.g. using dropbox.com)? Without seeing your workbook it's difficult to find right solution – Dmitry Pavliv
It's ok Simoco, i found the problem. I was just an idiot. Thanks – HumanlyRespectable

1 Answers

0
votes

If the values in column T contain positive values or negative values or a text string, but not zero then replace the text values with the value zero and format these zero cells as Custom

;;"Jon Not Finished";

These cells will show the text, but behave like zero!