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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

derevin@gmail.com - 27 Nov 2006 21:34 GMT

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.

Reply to this Message

Nick Hodge - 28 Nov 2006 07:57 GMT

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)

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk

> Hello,
>
[quoted text clipped - 14 lines]
> 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.

Reply to this Message

derevin@gmail.com - 01 Dec 2006 21:14 GMT

Nick,

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

> Derevin
>
[quoted text clipped - 42 lines]
> > 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.

Reply to this Message

Rate this thread: