+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    daviesnc
    Guest

    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 !!

  2. #2
    Bob Phillips
    Guest

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

    =SUMIF(A:A,"DEL",C:C)

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "daviesnc" <daviesnc@discussions.microsoft.com> wrote in message
    news:92963E58-ADAB-4ED7-83B4-B4F62D324ED4@microsoft.com...
    > 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 !!




  3. #3
    Sandy Mann
    Guest

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

    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

    sandymann2@mailinator.com
    Replace@mailinator.com with @tiscali.co.uk


    "daviesnc" <daviesnc@discussions.microsoft.com> wrote in message
    news:92963E58-ADAB-4ED7-83B4-B4F62D324ED4@microsoft.com...
    >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 !!




  4. #4
    Sandy Mann
    Guest

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

    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

    sandymann2@mailinator.com
    Replace@mailinator.com with @tiscali.co.uk


    "Sandy Mann" <sandymann2@mailinator.com> wrote in message
    news:u%23IftW1WGHA.196@TK2MSFTNGP04.phx.gbl...
    > 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
    >
    > sandymann2@mailinator.com
    > Replace@mailinator.com with @tiscali.co.uk
    >
    >
    > "daviesnc" <daviesnc@discussions.microsoft.com> wrote in message
    > news:92963E58-ADAB-4ED7-83B4-B4F62D324ED4@microsoft.com...
    >>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
    >> !!

    >
    >





  5. #5
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    3,993
    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

  6. #6
    daviesnc
    Guest

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1