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.

 

[tennis07]

but is there an X next to the the column to the right every time a fifth value appears? because the sumproduct formula depends on those X's

Yes, there is, but if not, I guess your solution should work as well, right?

 

[dave2018]

Yes, there is, but if not, I guess your solution should work as well, right?

i think Aladin posted it.

but you should for sure go with marco's in my opinion

all the best!

 

[tennis07]

i think Aladin posted it.

but you should for sure go with marco's in my opinion

all the best!

but is there an X next to the the column to the right every time a fifth value appears? because the sumproduct formula depends on those X's

Edit: Dave, just to continue: When I type:

Color: Red
Values: 4
SUM: 141 (I should get 139)

It assumes that the 5th cell of the 3rd red group is a new group (4th). So, when I type Values: 3 or Values: 4, I should get 139 for both.
Its a bit trick because If I type a value for a color bigger than I actually have, I will get a wrong result.

All the best for you all guys!
Thanks everyone

 

[dave2018]

Edit: Dave, just to continue: When I type:

Color: Red
Values: 4
SUM: 141 (I should get 139)

It assumes that the 5th cell of the 3rd red group is a new group (4th). So, when I type Values: 3 or Values: 4, I should get 139 for both.
Its a bit trick because If I type a value for a color bigger than I actually have, I will get a wrong result.

All the best for you all guys!
Thanks everyone

I didnt understand. Is this a problem you need to fix? or did you figure it out?

 

[tennis07]

Lets try to explain a bit better.
The marcelo's solution probably works fine, but I was wondering how to solve this issue if I didnt have anything (X) to identify the fifth line of a group. I though the solution with { was working properly, but when I added more data at the end, it got lost.

 

[dave2018]

i will give you a function for column G, that will populate an X at the right place.

but:
will color A AND B always have the same borders? ie on row 27 of your last image, both colors end on the same row. is that accross the board of ur data?

ps. which website do u use to upload your photo?

 

[tennis07]

i will give you a function for column G, that will populate an X at the right place.

but:
will color A AND B always have the same borders? ie on row 27 of your last image, both colors end on the same row. is that accross the board of ur data?

ps. which website do u use to upload your photo?

Dave, it can be randon once I populate that every single day, but there is a pattern as you can see. A group has always 4 or 5 cells in a row with the same text color.

So,
Column A will be

Green / Green / Green / Green (4)
Red / Red / Red / Red / Red (5)
Green / Green / Green / Green (5)
Red / Red / Red / Red / Red (4)
Green / Green / Green / Green (4)

I will never have more than 5 in sequence for the same column.
I can have like:

Column A
A21 Red
A22 Red
A23 Red
A24 Red (4th)

B25 Red
B26 Red
B27 Red
B28 Red
B29 Red (5th)

It contains 9 red in a row but I dont consider that once 4 are on column A and 5 on column B. So, there are 2 groups of red color and the column B29 should not be calculated.

PS: imgur.com

 

[dave2018]

Ok,

based on the fact that all colors on BOTH columns begin and end with each other, put this formula in G1, and then copy down...

(i changed some data for my testing...)

Excel 2016 (Windows) 32 bit
	B	C	D	E	F	G
1	blue	Red		10	5
2	blue	Red		20	10
3	blue	Red		30	15
4	blue	Red		40	20
5	grey	blue		2	4
6	grey	blue		4	8
7	grey	blue		6	12
8	grey	blue		8	16
9	grey	blue		15	2	X
10	yellow	Red		30	2
11	yellow	Red		50	2
12	yellow	Red		60	2
13	yellow	Red		15	7
14	white	yellow		30	7
15	white	yellow		35	7
16	white	yellow		40	8
17	white	yellow		5	8
18	white	yellow		5	9	X
Sheet1

Cell Formulas
Range	Formula
G1	{=IF((ROWS($C$1:C1)-IFERROR(MATCH("zzzz",IF($C$1:C1<>C1,$C$1:C1)),0))=5,"X","")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dave2018]

let me know if this solves the problem

 

[jtakw]

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.

Book1
	B	C	D	E	F	G
1	Color A	Color B		S1	S2
2	Blue	Red		10	5
3	Blue	Red		20	10
4	Blue	Red		30	15
5	Blue	Red		40	20
6	Grey	Blue		2	4
7	Grey	Blue		4	8
8	Grey	Blue		6	12
9	Grey	Blue		8	16
10	Yellow	White		15	2
11	Yellow	White		30	2
12	Yellow	White		50	2
13	Yellow	White		60	2
14	White	Yellow		15	7
15	White	Yellow		30	7
16	White	Yellow		35	7
17	White	Yellow		40	8
18	Red	Green			8
19	Red	Green			9
20	Red	Green			10
21	Red	Green			11
22	Red	Green			100	X
23	Green	Red			3
24	Green	Red			15
25	Green	Red			25
26	Green	Red			26
27	Green	Red			2	X
28	Yellow	Blue			2
29	Yellow	Blue			2
30	Yellow	Blue			2
31	Yellow	Blue			2
Sheet424

Cell Formulas
Range	Formula
G6	=IF(COUNTIF(C2:C6,C6)=5,"X","")

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...