Sum Cells if Another Cell Meets Criteria and Stop When Count is Met

We were so close!. But it appears that the assumed correct answer only works if there are no repeating N. The repeating N gets the same count as the last Y and it throws off the sum.

Please help again...


I have data in two seperate columns. For Example,
Rank........Column B2:B4........Column D2:D4
1...........300.....................Y
2...........200.....................Y
3...........100.....................N
4...........50.......................Y

Column D1 holds a value, say 2

I want to sum the numbers in B2:B4 if the corresponding value in D is Y, but only sum up to the number of in D1. In this case 2.

So the result should be 250

How do I do this sum and stop when another criteria is met.

Also note that I will be repeating this process across several Columns such as D2:D4. B2:B4 will be constant, but I will have E1 value and E2:E4 range, F1 value and F2:F4 range etc...

Not sure if you mean where the Rank is above the number in D2...cause that adds up to 250?

Anyways....

=Sumproduct((Rank_range>=D$1)*(D$2:D$4="Y"),$B$2:$B$4)

copied down and perhaps across.

Originally Posted by obatiz

I have data in two seperate columns. For Example,
Rank........Column B2:B4........Column D2:D4
1...........300.....................Y
2...........200.....................Y
3...........100.....................N
4...........50.......................Y

Column D1 holds a value, say 2

I want to sum the numbers in B2:B4 if the corresponding value in D is Y, but only sum up to the number of in D1. In this case 2.

So the result should be 250

I hope I spotted some errors in your description, because the only way I got 250 was to Sum rankings from the bottom UP, AND to expand the range to B2:B5, etc..

This formula will do it for the D column:

=SUMPRODUCT(($A$2:$A$5>=D$1)*(D$2:D$5="Y")*($B$2:$B$5))

I put that formula in D6. You can copy that across row 6 and it will work for the next row, as long as there are Y/N answers above it and and a reference in row 1.

Originally Posted by obatiz

Thank you for the quick responses. I apologize for not being clear, it is my first time doing this.

You are correct the only way to get 250 is to count from the bottom. So starting with my lowest rank in this case 4 ,sum the b column values for when Y is found in D. The key is to stop the D1 value has been met, in this case two. So I would sum rank 4 (50) and rank 2 (200) to get the 250.

So then the formulas supplied should work... in mine, you would replace Rank_range with an actual range (same size as column B and D ranges).

Did you try mine? It works from the bottom up as desired, using your D1 cell as a stop, again, as described.

Ah, ok, then technically the first one isn't working either, it's just accidentally getting the right answer. I viewed C1 as the highest rank position acceptable, you meant it as "number of allowable matches". Not the same thing.

I'll have to think about it.

How big/small is the field in question?

Is it always limited to 4 rows?

Also note that I will be repeating this process across several Columns such as D2:D4. B2:B4 will be constant, but I will have E1 value and E2:E4 range, F1 value and F2:F4 range etc...

Descriptions like that tend to throw me off unless I can see them in a worksheet, which I think would be helpful, at least for me to give you a hand. JB seems to have a pretty good handle on it though.

Does this work for you?... it's a bit brute force... but I think it works....

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER.. You will see { } brackets appear. Then copy across the columns.

Originally Posted by NBVC

Does this work for you?... it's a bit brute force... but I think it works....

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER.. You will see { } brackets appear. Then copy across the columns.

I get a #NUM error when I tried that directly. And I tried to expand that to a larger data set, which is the implication of the question, and I can't expand it properly.

I can do this easily with a helper column to Rank the Y answers, simple then. Would that be acceptible? You could hide those columns afterward to keep the appearance clean. Just a thought. (attached)

Originally Posted by JBeaucaire

I get a #NUM error when I tried that directly. And I tried to expand that to a larger data set, which is the implication of the question, and I can't expand it properly.

See attached...

Great formula, NBVC.

I know my helper column worked, but NBVC's solution doesn't require it. Just change all the C$5 references to C$100 or whatever. Much nicer. Go with that.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

Originally Posted by obatiz

Helper column is fine.

You marked this thread as NOT SOLVED. Did you try the solution in the workbook posted?

=SUM(IF(INDEX(C$2:C$5,SMALL(IF(C$2:C$5="y",ROW(C$2:C$5)-ROW(C$2)+1),
MAX(0,COUNTIF(C$2:C$5,"y")-C$1)+1)):C$5="Y",INDEX($B$2:$B$5,
SMALL(IF(C$2:C$5="y",ROW(C$2:C$5)-ROW(C$2)+1),
MAX(0,COUNTIF(C$2:C$5,"y")-C$1)+1)):$B$5))

This formula can be copied and the C$5 can be expanded to C$100 or whatever to cover as deeply as you need. It looks in C$1 for the number of matches you want. And if you copy this formula to D, E, etc, it will adjust itself.

This one works.

As does this one:

=SUM(IF(ROW($C$2:$C$15)>=LARGE(IF($C$2:$C$15="y",ROW($C$2:$C$15)),
MIN(5,COUNTIF($C$2:$C$15,"Y"))),IF($C$2:$C$15="Y",$B$2:$B$15))

Source: Aladin Akyurek at MrExcel

From PM:

... I have more than the 4 rows of your excel example and I am having trouble changing your formula when my range is 20. Since I have more than 4 is in not as simple as changing the cell start and ends from yours or do I have to add more iterations to your formula as the number of rows grow.

Yes, you need to just change the ranges throughout the formula... and remember to re-confirm it with CTRL+SHIFT+ENTER not just ENTER... then copy it across..

For a range from row 2 to row 21 (20 rows)...try:

Please Login or Register  to view this content.