Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Sum (Excel 97, 2000)

    See attached File. I can get conditional sum to work for rows 5-15 but can't
    seem to expand to correctly cover rows 16 thru 18. Any thoughts would be
    greatly appreciated. A generic answer that would allow expansion or
    contraction to cover any number of rows is what I really am interested in.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (Excel 97, 2000)

    I'm not sure what your problem is. The following formula, array entered (hold down Ctrl+Shift when you press enter), works for me:

    <pre>=SUM(IF($A$5:$A$31=F4,$D$5:$D$31,0))
    </pre>


    The following also works (it does not have to be array entered):

    One other point. The Down Time and Totat DT columns are elapsed times, not time of day. If there is any chance that any of these times could exceed 24 hours, then you should be using a Custom format of [h]:mm (with the brackets around the h) or you will display erroneous results.

    <pre>=SUMIF($A$5:$A$31,F4,$D$5:$D$31)
    </pre>


    What problem are you having?
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (Excel 97, 2000)

    The second formula, the one without array entry, works fine. The array formua when entered as the first entry in column calculates correctly. However, trying to copy the formula down from that point down the column results in N/A errors. Not sure what is going on there.

    Thanks for the prompt assistance. The first formula gets me what I need and is most appreciated.

    Thomas Cox

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (Excel 97, 2000)

    When there is just one condition/criterion, there is seldom any need for using an array formula to compute a sum/total (or a count for that matter). SUMIF for summing and COUNTIF for counting will suffice.

    Legare has already suggested the required SUMIF formula.

    However, if you want the additions to and/or deletions from the downtime data to be reflected automatically in the computed totals, you could do what follows. (See also the attachment.)

    ( 1) Insert a new worksheet named Admin.
    ( 2) In Admin!A2 enter: # Data Recs [ which is just a label ].
    ( 3) In Admin!B2 enter: =MATCH(9.99999999999999E+307,Data!B:[img]/forums/images/smilies/cool.gif[/img]-(CELL("Row",Data!A5)-1)
    ( 4) Activate the option Insert|Name|Define.
    ( 5) Enter DTdata as name in the Names in Workbook box.
    ( 6) Enter as formula in the Refers to box:

    =OFFSET(Data!$A$5,0,0,Admin!$B$2,4)

    ( 7) Activate OK.

    In the worksheet where you have the downtime data use the following formula:

    =SUMIF(INDEX(DTdata,0,1),F4,INDEX(DTdata,0,4))

    This is the same thing as the one Legare has suggested. The difference is that it uses the defined name from the step 5 above instead of definite ranges: INDEX(DTdata,0,1) means all rows of DTdata from its first column and INDEX(DTdata,0,4) all rows from its 4th column.

    Why all this? The advantage of this setup is that you don't have to change the formula when you add more records to the downtime data area or when you delete records from that area.

    I'd suggest also that you make the charts dynamic. Bullen explains well how to do that at his site with examples:

    [url]http://www.bmsltd.ie/Excel/Default.htm[url]


    Aladin

    Edited Mar 13th 2004 to update link
    Attached Files Attached Files
    Microsoft MVP - Excel

Posting Permissions

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