How to tell excel to keep summing up values when a condition is met

Hi SaraHenry,

Thanks for contacting us,

To achieve the result you want, you need to use a formula that can sum up the values in column B based on the condition in column A. One possible formula is the SUMIF function, which can sum the values in a range that meet a certain criteria.

However, the SUMIF function alone is not enough, because you also need to reset the sum whenever there is a number "9" in column A. To do that, you need to use another function that can count how many times the number "9" appears in column A up to the current row. One possible function is the COUNTIF function, which can count the number of cells in a range that meet a certain criteria.

By combining the SUMIF and COUNTIF functions, you can create a formula that can sum up the values in column B based on the condition in column A and reset the sum whenever there is a number "9" in column A. Here is an example of such a formula:

=SUMIF (A$2:A2, "<>9", B$2:B2) - SUMIF (A$2:A2, "<>9", B$2:B2) * COUNTIF (A$2:A2, "=9")

This formula works as follows:

• The first SUMIF function sums up the values in column B from row 2 to the current row, excluding the rows where column A is "9".
• The second SUMIF function does the same thing, but multiplies the result by the number of times the number "9" appears in column A from row 2 to the current row, using the COUNTIF function.
• The formula then subtracts the second SUMIF result from the first SUMIF result, effectively resetting the sum whenever there is a number "9" in column A.

To use this formula, you need to enter it in cell C2 and then copy it down to the rest of the column. You can use the fill handle or the keyboard shortcut Ctrl+D to do that. You should see the result you want in column C.

Let me know if this helps or if you need further assistance.

Regards, Sola

“Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below. “

Kindly note that this is a user to user forum, we are users helping other users, we aren't Microsoft employee neither are we Microsoft agents.

You are welcome. If do not understand your requirement at all.

Hi Ashish,

Thank you for your help, this works well for when the "9" and the "1" meet in columns A and B. However there is a case that i forgot to mention where the 1 starts after the 9. as in the example below

in summary, column C should start counting the 1s only if two conditions are true:

CONDITION 1

A13=9 and B=13

CONDITION 2

example: A13=9 and (B13=1 or B12=1)

Hi,

In cell D2, enter this formula and drag down

=IF(B2=0,"",IFERROR(SUM(B2:XLOOKUP(9,A2:A$25,B2:B$25)),""))

Hope this helps.

Hi Sola,

Thank you for your quick reply!

However, unfortunately the formulae didnt calculate the results i wanted. See comparison below. I think my previous example might have been confusing. Let me reiterate.

If you look at the "results needed" column C, It should start counting the "1s" in column B, only if we see a "9" in column A, and keep counting until we see another "9" in column A, I want it to keep counting until there is another "9" and the count should start from the beginning.

Below, you will see cell A6 is the first "9" in column A, so column C should start counting from cell C6 until C12 (counting the "1s" in column B), and the counting should continue until we see another "9" in column A cell A13, the counting should start over.

Thank you for your help

Sara