software

I have 3 columns of data in a spreadsheet, and I need to create a report

Example-
Airline DelayCode Time
ABC - DEL - 2.00
DEF - DEL - 1.33
ABC - REJ - 3.44
GHI - DEL - 3.50
ABC - XYZ - 1.05

What I need is a report of the number showing the number of time that
airline ABC was delayed. how many times DEL, or REJ (or whatever) appears,
and what the total time was, per code , and as a grand total. (Here, ABC was
delayed 3 times in total. Twice for 'DEL' (time: 5:50), and once for XYZ
(time: 1:05); Grand TOTAL of delays = 6:55 (5:50 1:05)

I will then make charts from the data, I'd really appreciate some help with
this as I don;t even know the terminology of the thing I'm trying to do !!

=SUMIF(A:A,quot;DELquot;,C:C)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;daviesncquot; gt; wrote in message
...
gt; I have 3 columns of data in a spreadsheet, and I need to create a report
gt;
gt; Example-
gt; Airline DelayCode Time
gt; ABC - DEL - 2.00
gt; DEF - DEL - 1.33
gt; ABC - REJ - 3.44
gt; GHI - DEL - 3.50
gt; ABC - XYZ - 1.05
gt;
gt; What I need is a report of the number showing the number of time that
gt; airline ABC was delayed. how many times DEL, or REJ (or whatever) appears,
gt; and what the total time was, per code , and as a grand total. (Here, ABC
was
gt; delayed 3 times in total. Twice for 'DEL' (time: 5:50), and once for XYZ
gt; (time: 1:05); Grand TOTAL of delays = 6:55 (5:50 1:05)
gt;
gt; I will then make charts from the data, I'd really appreciate some help
with
gt; this as I don;t even know the terminology of the thing I'm trying to do !!
daviesnc,

With the headers of your data in A1:C1 and the data in A2:C6, I created a
table with DelayCode's as headers, (DEL,REL,XYZ), in H15:J15 and Airline
names in G16:G18. Then in H16 I entered the formula:

=SUMPRODUCT(--($A$2:$A$6=$G16),--($B$2:$B$6=H$15),$C$2:$C$6)

and copied it down and across to fill in the table and then sum the
columns/Rows in the normal way.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;daviesncquot; gt; wrote in message
...
gt;I have 3 columns of data in a spreadsheet, and I need to create a report
gt;
gt; Example-
gt; Airline DelayCode Time
gt; ABC - DEL - 2.00
gt; DEF - DEL - 1.33
gt; ABC - REJ - 3.44
gt; GHI - DEL - 3.50
gt; ABC - XYZ - 1.05
gt;
gt; What I need is a report of the number showing the number of time that
gt; airline ABC was delayed. how many times DEL, or REJ (or whatever) appears,
gt; and what the total time was, per code , and as a grand total. (Here, ABC
gt; was
gt; delayed 3 times in total. Twice for 'DEL' (time: 5:50), and once for XYZ
gt; (time: 1:05); Grand TOTAL of delays = 6:55 (5:50 1:05)
gt;
gt; I will then make charts from the data, I'd really appreciate some help
gt; with
gt; this as I don;t even know the terminology of the thing I'm trying to do !!
I forgot that you also wanted a count of the delays:

move the column of airline names created below from G15:G19 to F15:F19 and
in G16 eneter the formula:

=COUNTIF($A$2:$A$6,F16)

and copy down.If you have a very large amount of data you could also create a Pivot Table
but Pivot Tables do not update automatically

--
HTH

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;Sandy Mannquot; gt; wrote in message
...
gt; daviesnc,
gt;
gt; With the headers of your data in A1:C1 and the data in A2:C6, I created a
gt; table with DelayCode's as headers, (DEL,REL,XYZ), in H15:J15 and Airline
gt; names in G16:G18. Then in H16 I entered the formula:
gt;
gt; =SUMPRODUCT(--($A$2:$A$6=$G16),--($B$2:$B$6=H$15),$C$2:$C$6)
gt;
gt; and copied it down and across to fill in the table and then sum the
gt; columns/Rows in the normal way.
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt; In Perth, the ancient capital of Scotland
gt;
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;daviesncquot; gt; wrote in message
gt; ...
gt;gt;I have 3 columns of data in a spreadsheet, and I need to create a report
gt;gt;
gt;gt; Example-
gt;gt; Airline DelayCode Time
gt;gt; ABC - DEL - 2.00
gt;gt; DEF - DEL - 1.33
gt;gt; ABC - REJ - 3.44
gt;gt; GHI - DEL - 3.50
gt;gt; ABC - XYZ - 1.05
gt;gt;
gt;gt; What I need is a report of the number showing the number of time that
gt;gt; airline ABC was delayed. how many times DEL, or REJ (or whatever)
gt;gt; appears,
gt;gt; and what the total time was, per code , and as a grand total. (Here, ABC
gt;gt; was
gt;gt; delayed 3 times in total. Twice for 'DEL' (time: 5:50), and once for XYZ
gt;gt; (time: 1:05); Grand TOTAL of delays = 6:55 (5:50 1:05)
gt;gt;
gt;gt; I will then make charts from the data, I'd really appreciate some help
gt;gt; with
gt;gt; this as I don;t even know the terminology of the thing I'm trying to do
gt;gt; !!
gt;
gt;


Hi,

In addition to Sandy's very elegant solution, I would recommend using a
pivottable, especially if you want to produce graphs afterwards ...

HTH
Carim--
Carim
------------------------------------------------------------------------
Carim's Profile: www.excelforum.com/member.php...oamp;userid=33259
View this thread: www.excelforum.com/showthread...hreadid=531197Firstly , thank you all for previous assistance - however I think the
solution you proferred, though excellent not workable give the huge amount of
data i have to sort, here is a piece (there are 14680 rows in the full
version !!). What I need to do is

1) Show the total number of instances of each DELAY (ALTGET, and so-on) PER
AIRLINE, and a GRAND TOTAL of each DELAY type for all airlines.

So, in the example below, airline ABY had 2 instances of the ALTGET delay,
airline ACI had one instance of ALTGET. Grand Total = 3 instances of ALTGET.

A1 B1 C1 etc etc..
AIRLINE DELAY TIME
______ _____ ____
ABYALTGET -0.19
CLIMM -0.55
ALTGET -0.06
FLPOSS -0.04
FPCLC -0.08
NALSL -0.63
PARMAA -0.02
PRTPLN -2.69
RCRFFL -0.02
SECDES -0.03
TRGIN1 -0.02
XSPTPT -0.01
ZCRTAB -0.02
ABY Total4.36
ACIALTGET -0.27
CH2INT -0.03
CLCALC -0.02
CLIMM -0.65
FLPDT -0.07
FPCLC -0.02
PARMWX -0.15
PRTPLN -0.3
VERALT -0.01
YSTART -0.04

Thanks again for your valuable assistance