Sum column based on values in 2 other columns

[Deuce08]

Looking for a formula that would sum a column based on Values in 2 other columns

Column A is a name, Column B is if there are any errors, could be blank or any numerical value, and Column C is a case amount, a numerical value. I would like to sum Column C for "Mike" in Column A until there is an error in Column B, and not include that value in Column C for the row that the error occurred in the summation.

Thanks so much!

[Bernie Deitrick]

I would like to sum Column C for "Mike" in Column A until there is an error in Column B,

An Error for Mike only or any error? And if Mike / valid B / value in C occurs after the error, should it be included with Mike's total?

[Deuce08]

Error for Mike only,there will be many names that I will copy the formula to and track for each person. I don't want to include any value in C for the row of the error or after the error. What I am trying to do is calculate a running total until an error occurs in Colum B. I want to be able to look and see how many cases each person has since their last error. Each new row will go at the top, so it will be looking top down for the most recent error, and adding colum C to that point.

[JeteMc]

Please read the yellow banner at the top of this page on how to attach an Excel file.
Let us know if you have any questions.

[Deuce08]

In the attachment, want to sum cases up to the latest error, which will always be top down with most recent at top. In this case want the three case values that equal 581. Do not want to include the value for the row that had the error.

[Glenn Kennedy]

A guess, as you did not show how/where you wanted the results presented.

=SUMIF(A2:INDEX($A2:$A$23,MATCH(1,($A$2:$A$23=F2)*($B$2:$B$23=1),0)-1),F2,C2:INDEX($C$2:$C$23,MATCH(1,($A$2:$A$23=F2)*($B$2:$B$23=1),0)-1))

see sheet.

[Bernie Deitrick]

With your list of assemblers starting in G2, array-enter (enter using Ctrl-Shift-Enter)

=IFERROR(SUMPRODUCT(($C$2:$C$23)*($A$2:$A$23=G2)*(ROW($C$2:$C$23)<(MATCH(G2&"1",$A$1:$A$23&$B$1:$B$23,FALSE)))),SUMIF(A:A,G2,C:C))

And copy down to match your assemblers list, as in the attached.

[Deuce08]

Thanks for both those options,as I'll be adding a column on a sheet I think Bernie's works best. My question is, the error column could be any number, it would need to work when that error value in column B is a 2, or a 10, or any value. How do I adjust the formula to accommodate that?

Thanks for the help!

[Glenn Kennedy]

If I understand you correctly... a tiny tweak:

see file

[Deuce08]

Doesn't look like that one is working, shows correct for Mike, but it should show 144 for Dan, shows nothing. And for Chuck it shows 313, but should show just 233 as he made an error on the second one that had the 80 cases.

[JohnTopley]

Try

=SUM(SUMIFS($C$2:$C$23,$A$2:$A$23,G2,$B$2:$B$23,{"",0}))

[Bernie Deitrick]

Array-enter this in my cell H2, using Ctrl-Shift-Enter:

=IFERROR(SUMPRODUCT(($C$2:$C$23)*($A$2:$A$23=G2)*(ROW($C$2:$C$23)<(1/(1/MIN(IF($A$2:$A$23=G2,IF($B$2:$B$23>0,ROW($B$2:$B$23)))))))),SUMIF(A:A,G2,C:C))

[Deuce08]

That worked. Last question, I hope. This list will grow each week, it looks like the sum of Cases grows as I add lines for each person, but it doesn't respect the errors in column B. I tried changing formula to the entire column instead of just to row 23 but that didn't work. Is that possible, or should I just update the formula to the exact row each time?
Thanks

[Bernie Deitrick]

Insert new rows into your used range, and the formula will expand. You cannot use entire columnw with array formulas.

[Deuce08]

Perfect, that's great, thanks again

[JohnTopley]

The SUMIFS in post #11 gives the same result as Bernie's formula: am I missing something?

[Bernie Deitrick]

Your formula fails if there are 0 or blank values in B, in lower rows for the same names that have errors up higher. The values from C get added to the total for those rows.

[JohnTopley]

In attached column H & I results are identical ????

[Bernie Deitrick]

Simplified name list - lots of "Mike" entries. The OP described in words what he wanted, but his data set didn't reflect the 'reset' that he expected.

[wk9128]

Borrow POST#19 Bernie Deitrick'S attachment file

Another way

Cell J2 formula , drag down

Please Login or Register  to view this content.

[Bernie Deitrick]

This formula also fails if there are 0 or blank values in B, in lower rows for the same names that have errors up higher. The values from C get added to the total for those rows.