YAPTQ: Mixed data types in custom groups (Excel 2003)

[derevin]

Hello,

I'm trying to get the following accomplished in Excel 2003:
I have a column in a table that keeps track of how late a form was
turned in. It can have an integer (for the number of days it was late)
or one of several text strings ("On time," "Cannot determine," "Outside
report dates.")

I need to be able to generate the totals for:
On time or within 7 days late,
8-15 days,
More than 15 days
Cannot determine
Outside report dates

It seems like it should be a straightforward procedure, where one can
specify a range of numbers, or a list of values, but I haven't been
able to find a way to get Excel to do this.

 

[Nick Hodge]

Derevin

I have assumed your data is in A1:A1000

Set up a table (I've assumed J1:J5) with

<7
<15

15

Cannot determine
Outside report dates

and set up the following formulas alongside

=COUNTIF($A$1:$A$1000,"<=7")
=SUMPRODUCT(($A$1:$A$1000>7)*($A$1:$A$1000<=15))
=COUNTIF($A$1:$A$1000,">15")
=COUNTIF($A$1:$A$1000,J4)
=COUNTIF($A$1:$A$1000,J5)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[derevin]

Nick,

That's beautiful, especially the clever use of SUMPRODUCT. Thank you
so much!