SUMPRODUCT Error

[babuda]

I got 3 columns of data for ex
col f6:f19 is defect severity can be 1 or 2 or 3
col c6:c19 is defect status "Open" or "Closed"or "Pending"
col d6:d19 is age of defect . the data in column D is derived by a formula using today()-(g6:g19) . some data in column is hardcoded and some is not

I need to create a matrix to find out total defects status =Open, Sev = 1 or 2 or 3 and age = <10 days,<20 days,>30 days.

The problem i am facing is there is no data in some cells because status=closed.
I used this formula and this worked for only one or 2 cells where there is uniform data.

=SUMPRODUCT(F6:F19=3,C6:C19="Open",D6:D19<=10)

I get either 0 or #VALUE error . 0 is not correct and it should give me 4.

is there a way out if the data in underlying cells is not uniformly formatted?


Thanks

[zbor]

Add 1*

=SUMPRODUCT(1*(F6:F19=3)),1*(C6:C19="Open"),1*(D6:D19<=10))

or --

=SUMPRODUCT(--(F6:F19=3)),--(C6:C19="Open"),--(D6:D19<=10))

[shg]

Or =SUMPRODUCT((F6:F19=3) * (C6:C19="Open") * (D6:D19<=10))

[babuda]

Thank you friends but it did not work out,so I am uploading the actual data file!

Thanks

[JBeaucaire]

Your Severity column needs to be cleaned up. There are "2" and "2 " (with a space in there).

Use a dropbox validation list with your options 1,2,3,4 for severity so people won't type junk into that column. Once the corruption is cleaned, the SUMPRODUCT works as designed.

[babuda]

Thank you very much, but still I got to solve this problem which I think I cannot! The data is copied from ClearQuest and too many people handle this file for which I got no control.

Babuda

[JBeaucaire]

Sure there's a solution. Fix it yourself, same as I did in the version I uploaded.