SUM last N values based in criteria

[tennis07]

Hi guys,

Does anyone could help me?

What I need is to type a color and the last N values to be calculated.

N values = 1*4 = last 4 values

So, if I enter 1, it needs to get the last 4
2 = 8
3 = 12
and so on...

Sometimes a color has 5 cells but the fifth is always marked with an X and it is not to be calculated.
Follows the example below:

Thanks in advance.

 

[dave2018]

Hi,

If you're wanting to populate Column G with an "X", for the 5th occurrence of the Same color in the Same Group...
You can use this formula in G6 copied down.

	B	C	D	E	F	G
Color A	Color B	S1	S2
Blue	Red
Blue	Red
Blue	Red
Blue	Red
Grey	Blue
Grey	Blue
Grey	Blue
Grey	Blue
Yellow	White
Yellow	White
Yellow	White
Yellow	White
White	Yellow
White	Yellow
White	Yellow
White	Yellow
Red	Green
Red	Green
Red	Green
Red	Green
Red	Green	X
Green	Red
Green	Red
Green	Red
Green	Red
Green	Red	X
Yellow	Blue
Yellow	Blue
Yellow	Blue
Yellow	Blue

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet424​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=IF(COUNTIF(C2:C6,C6)=5,"X","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



PS. A reminder, Aladin in Post # 2 had asked you to upload a "readable" table (something your helpers can copy and paste into Excel for testing), rather than a picture...

Genius! I didnt think of making a moving array with 5 cells

 

[tennis07]

Thanks guys!
For the last two posts, the formulas to mark with an "X" worked well.

I really appreciate.
Thanks everyone who came here and helped me.

 

[Aladin Akyurek]

1) Try to post data/sample which I don't have retype.

2) What do you mean by 1, 2, etc.? Are they values of S1 or S2? What is then the sumrange?

 

[jtakw]

You're welcome, glad we could help.

Thanks dave2018.