Conditional Sum (Excel 97, 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

=SUM(IF($A$5:$A$31=F4,$D$5:$D$31,0))

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.

=SUMIF($A$5:$A$31,F4,$D$5:$D$31)

What problem are you having?

Reply | Quote

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

Reply | Quote

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:-(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:

http://www.bmsltd.ie/Excel/Default.htm

Aladin

Edited Mar 13th 2004 to update link

Reply | Quote