CODEWITHSUNDEEP
excelexcel-formula
tony971
tony971

Reputation: 3

Using SUMPRODUCT with Value Manipulation

I'm trying to count cells in a column that contain either numbers between 0000 and 2400 or "TBD". The actual column entries contain numbers, "ddmmm" dates, and text in no particular order. The entire column is force formatted into text.

Without including any true text entries, the following formula works:

=SUMPRODUCT(--(VALUE(H:H)>0)*--(VALUE(H:H)<2401))

But once I start throwing text entries in, VALUE obviously #VALUE!'s out. I'm having trouble getting it to work in a range that throws errors for the VALUE function.

Tl;dr: I need to count entries in a text-formatted column that contain numbers 0000-2400 or the text "TBD". I've hit a wall with entries that can't be converted with VALUE.

Upvotes: 0

Views: 99

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

Use this Array Formula

=SUM(IFERROR((--H:H>0)*(--H:H<2401),0)+(H:H="TBD"))

Being an Array Formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Upvotes: 1

Alex Marks
Alex Marks

Reputation: 76

So, VALUE("0000") gives you 0 and VALUE("2400") gives you 2400, so that means that those will work as you expect. The issue is probably when you run into TBD or any other value.

You need to use an IF statement to catch it before it evaluates VALUE() and errors out.

=IF(ISERR(VALUE(<cell>)),IF(<cell>="TBD",<do TBD stuff here>,<entry is not a date or TBD>),SUMPRODUCT(...))

Little easier to read in "classic" if statement format:

IF (VALUE(<cell>) = ERR)
{
    IF (<cell> = "TBD")
    {
        <do TBD stuff here>
    }ELSE{
        <entry is not a date or TBD>
    }
}ELSE{
    SUMPRODUCT(...)
}

Upvotes: 0

Related Questions