I've 5 columns, of assorted items.how do I tally them up?

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,"DEL",C:C)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"daviesnc" <[email protected]> wrote in message
news:[email protected]...
> 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 !!

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

[email protected]
[email protected] with @tiscali.co.uk


"daviesnc" <[email protected]> wrote in message
news:[email protected]...
>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 !!

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

[email protected]
[email protected] with @tiscali.co.uk


"Sandy Mann" <[email protected]> wrote in message
news:u%[email protected]...
> 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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "daviesnc" <[email protected]> wrote in message
> news:[email protected]...
>>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
>> !!
>
>

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

Firstly , 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
______ _____ ____
ABY ALTGET - 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 Total 4.36
ACI ALTGET - 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