0

I have several thousand values similar to those shown in the picture provided.

I am attempting to sum the time values in column K, but only if they have a value of "flag" in column M, and only consecutive values.

For example Cells M467-M477 are all flagged, and when added together, they would equal ~0.017 hours.

Then Cells M478-M480 are ignored, then Cells M481-M483 are summed to equal ~0.0036 hours, and so on. What is an automated excel formula that will do this?

Example of Spreadsheet Values

![enter image description here

1
  • Where would each output go? – Scott Craner Jun 21 '16 at 0:29
0

An option with a helper column.

enter image description here

The formula in Q2 is

=IF(AND(M1<>"flag",M2="flag"),K2,IF(AND(M1="flag",M2="flag"),SUM(Q1,K2),""))

and in R2

=IF(Q3="",Q2,"")

Copy down.

0

If you want the sum to be in the first row of each group you can use this formula:

=IF(AND(M2="Flag",M1<>"Flag"),SUM(K2:INDEX(K2:INDEX(K:K,MATCH(1E+99,K:K)+1),MATCH(TRUE,INDEX(M2:INDEX(M:M,MATCH(1E+99,K:K)+1)="",),0)-1)),"")

Breaking it down:

The two INDEX(K:K,MATCH(1E+99,K:K)+1) find the last cell in column K that has a number and sets that as the extent of the referenced range This way the formula is dynamic, as new rows get added the formula will adjust automatically.

Then inside the Sum we start with the cell that is in the same row, the K2 is relative and as the formula moves down so does the reference. The end cell in the range is set with another INDEX/MATCH.

This time we are looking for the first cell from the row the formula is in to be empty in column M. We set that row -1 as the last row for the SUM().

The IF() merely makes any we want to do the sum and the rest to be an empty string.

enter image description here


If you want it located at the bottom of the group then use this array formula:

=IF(AND(M2="Flag",M3=""),SUM(INDEX($K$1:K2,IFERROR(MATCH(2,IF($M$1:M2="",1)),1)+1):K2),"")

Being an array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode. If done correctly the Excel will put {} around the formula.

This time we are looking for the last empty space in M above the current row to set the first.

The IFERROR is to deal with the title row, if the row just below has "Flag".

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.