SUM last N values based in criteria

T

tennis07

Board Regular
Joined
Apr 29, 2015
Messages
57
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.

FL3MNjF.png
 
Sort by date Sort by votes
Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td]
Color A​
[/td][td]
Color B​
[/td][td][/td][td]
S1​
[/td][td]
S2​
[/td][td][/td][td]
Color​
[/td][td]
Values​
[/td][td]
Sum​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td][/td][td]
Blue​
[/td][td]
Red​
[/td][td][/td][td]
10​
[/td][td]
5​
[/td][td][/td][td]
Red​
[/td][td]
2​
[/td][td]
89​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td][/td][td]
Blue​
[/td][td]
Red​
[/td][td][/td][td]
20​
[/td][td]
10​
[/td][td][/td][td]
Green​
[/td][td]
2​
[/td][td]
62​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td][/td][td]
Blue​
[/td][td]
Red​
[/td][td][/td][td]
30​
[/td][td]
15​
[/td][td][/td][td]
Red​
[/td][td]
3​
[/td][td]
139​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td][/td][td]
Blue​
[/td][td]
Red​
[/td][td][/td][td]
40​
[/td][td]
20​
[/td][td][/td][td]
Yellow​
[/td][td]
1​
[/td][td]
29​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td][/td][td]
Grey​
[/td][td]
Blue​
[/td][td][/td][td]
2​
[/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td][/td][td]
Grey​
[/td][td]
Blue​
[/td][td][/td][td]
4​
[/td][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td][/td][td]
Grey​
[/td][td]
Blue​
[/td][td][/td][td]
6​
[/td][td]
12​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td][/td][td]
Grey​
[/td][td]
Blue​
[/td][td][/td][td]
8​
[/td][td]
16​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td][/td][td]
Yellow​
[/td][td]
White​
[/td][td][/td][td]
15​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td][/td][td]
Yellow​
[/td][td]
White​
[/td][td][/td][td]
30​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td][/td][td]
Yellow​
[/td][td]
White​
[/td][td][/td][td]
50​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td][/td][td]
Yellow​
[/td][td]
White​
[/td][td][/td][td]
60​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td][/td][td]
White​
[/td][td]
Yellow​
[/td][td][/td][td]
15​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td][/td][td]
White​
[/td][td]
Yellow​
[/td][td][/td][td]
30​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td][/td][td]
White​
[/td][td]
Yellow​
[/td][td][/td][td]
35​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td][/td][td]
White​
[/td][td]
Yellow​
[/td][td][/td][td]
40​
[/td][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td][/td][td]
Red​
[/td][td]
Green​
[/td][td][/td][td]
5​
[/td][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td][/td][td]
Red​
[/td][td]
Green​
[/td][td][/td][td]
5​
[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td][/td][td]
Red​
[/td][td]
Green​
[/td][td][/td][td]
5​
[/td][td]
10​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td][/td][td]
Red​
[/td][td]
Green​
[/td][td][/td][td]
5​
[/td][td]
11​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
22​
[/td][td][/td][td]
Green​
[/td][td]
Red​
[/td][td][/td][td]
6​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
23​
[/td][td][/td][td]
Green​
[/td][td]
Red​
[/td][td][/td][td]
6​
[/td][td]
15​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
24​
[/td][td][/td][td]
Green​
[/td][td]
Red​
[/td][td][/td][td]
6​
[/td][td]
25​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
25​
[/td][td][/td][td]
Green​
[/td][td]
Red​
[/td][td][/td][td]
6​
[/td][td]
26​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
26​
[/td][td][/td][td]
Green​
[/td][td]
Red​
[/td][td][/td][td]
2​
[/td][td]
2​
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
27​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in J2 copied down
=SUMPRODUCT((ROW(B$2:B$26)>=LARGE((B$2:C$26=H2)*(G$2:G$26<>"X")*ROW(B$2:B$26),4*I2))*(G$2:G$26<>"X")*(B$2:C$26=H2),E$2:F$26)

Hope this helps

M.
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Marcelo Branco said:
Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[TD="bgcolor: #DCE6F1"]
J​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]
Color A​
[/TD]
[TD]
Color B​
[/TD]
[TD][/TD]
[TD]
S1​
[/TD]
[TD]
S2​
[/TD]
[TD][/TD]
[TD]
Color​
[/TD]
[TD]
Values​
[/TD]
[TD]
Sum​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD][/TD]
[TD]
Blue​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
10​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
2​
[/TD]
[TD]
89​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD]
Blue​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
20​
[/TD]
[TD]
10​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
2​
[/TD]
[TD]
62​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD][/TD]
[TD]
Blue​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
30​
[/TD]
[TD]
15​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
3​
[/TD]
[TD]
139​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD][/TD]
[TD]
Blue​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
40​
[/TD]
[TD]
20​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
1​
[/TD]
[TD]
29​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD][/TD]
[TD]
Grey​
[/TD]
[TD]
Blue​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD]
Grey​
[/TD]
[TD]
Blue​
[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD][/TD]
[TD]
Grey​
[/TD]
[TD]
Blue​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD][/TD]
[TD]
Grey​
[/TD]
[TD]
Blue​
[/TD]
[TD][/TD]
[TD]
8​
[/TD]
[TD]
16​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
White​
[/TD]
[TD][/TD]
[TD]
15​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
White​
[/TD]
[TD][/TD]
[TD]
30​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
White​
[/TD]
[TD][/TD]
[TD]
50​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
White​
[/TD]
[TD][/TD]
[TD]
60​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD][/TD]
[TD]
White​
[/TD]
[TD]
Yellow​
[/TD]
[TD][/TD]
[TD]
15​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD][/TD]
[TD]
White​
[/TD]
[TD]
Yellow​
[/TD]
[TD][/TD]
[TD]
30​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD][/TD]
[TD]
White​
[/TD]
[TD]
Yellow​
[/TD]
[TD][/TD]
[TD]
35​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD][/TD]
[TD]
White​
[/TD]
[TD]
Yellow​
[/TD]
[TD][/TD]
[TD]
40​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
Green​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
Green​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
Green​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
Green​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
25​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
25​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
26​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
26​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
X​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
27​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in J2 copied down
=SUMPRODUCT((ROW(B$2:B$26)>=LARGE((B$2:C$26=H2)*(G$2:G$26<>"X")*ROW(B$2:B$26),4*I2))*(G$2:G$26<>"X")*(B$2:C$26=H2),E$2:F$26)

Hope this helps

M.
Click to expand...

I had no idea that the data was set up with a column devoted for X...




this is so simple! :)
 
Last edited:
Upvote 0
Marcelo Branco said:
Well..i assumed there is such column ;)

From post 1


M.
Click to expand...

I read that, but didnt understand it clearly.

btw i didnt understand 2 things:
1)is that range static? or does it go further down the page?
2) can there be like, 5 yellows? or only the last colors have 5 values?

wtvr...

i had a good time playing with it ;)
 
Upvote 0
dave2018 said:
are you entering it as an array formula?

copy this into your cell then click CTRL+SHIFT+ENTER

if you look in the formula bar above, it should look like: {=SUM(IF(ROW(E2:F26)>=LARGE(IF(B2:C26=I1,IF(ISNUMBER(E2:F26),ROW(E2:F26))),MIN(I2*4,COUNTIFS(B2:C26,I1))),IF(B2:C26=I1,IF(ISNUMBER(E2:F26),E2:F26))))}
which confirms that it's an array formula.

otherwise, not sure why it isnt working :(
Click to expand...

Dave, its working now. When I was editing the formula, it took the { out.

PS: Could you explain step by step this formula? I'd like to understand all the process, and maybe use it in a different way when I need to.

Thank you guys.
 
Last edited:
Upvote 0
actually, you should use

=SUMPRODUCT((ROW(B$2:B$26)>=LARGE((B$2:C$26=H2)*(G$2:G$26<>"X")*ROW(B$2:B$26),4*I2))*(G$2:G$26<>"X")*(B$2:C$26=H2),E$2:F$26)

posted by marcelo branco in post 11.

simpler, and not array formula, meaning you dont need CTRL+SHIFT+ENTER
 
Upvote 0
tennis07 said:
Dave, its working now. When I was editing the formula, it took the { out.

PS: Could you explain step by step this formula? I'd like to understand all the process, and maybe use it in a different way when I need to.

Thank you guys.
Click to expand...

to see a formula working in "real-time", select the cell with the formula and open the "evaluate formula" window, which is found under the formulas tab. or ALT+M+V shortcut.

then, hit the space bar to evaluate the next part of the formula...

if you want further detailed explanation, just reply :)
 
Upvote 0
dave2018 said:
I read that, but didnt understand it clearly.

btw i didnt understand 2 things:
1)is that range static? or does it go further down the page?

2) can there be like, 5 yellows? or only the last colors have 5 values?




wtvr...

i had a good time playing with it ;)
Click to expand...

1) It goes further.
2) Its random.
Example:

yellow 1
yellow 2
yellow 3
yellow 4
yellow 5 (5th)
green
green
green
green
yellow 1
yellow 2
yellow 3
yellow 4

Color: yellow
Value: 2
SUM = (4 + 3 + 2 + 1) + (4 + 3 + 2 + 1)
Note that the 5th is off. (The color group is always with 4 or 5 but the fifth line of a group must be skipped.
 
Upvote 0
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
 
Upvote 0
Marcelo Branco said:
Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[TD="bgcolor: #DCE6F1"]
J​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]
Color A​
[/TD]
[TD]
Color B​
[/TD]
[TD][/TD]
[TD]
S1​
[/TD]
[TD]
S2​
[/TD]
[TD][/TD]
[TD]
Color​
[/TD]
[TD]
Values​
[/TD]
[TD]
Sum​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD][/TD]
[TD]
Blue​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
10​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
2​
[/TD]
[TD]
89​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD]
Blue​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
20​
[/TD]
[TD]
10​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
2​
[/TD]
[TD]
62​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD][/TD]
[TD]
Blue​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
30​
[/TD]
[TD]
15​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
3​
[/TD]
[TD]
139​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD][/TD]
[TD]
Blue​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
40​
[/TD]
[TD]
20​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
1​
[/TD]
[TD]
29​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD][/TD]
[TD]
Grey​
[/TD]
[TD]
Blue​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD]
Grey​
[/TD]
[TD]
Blue​
[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD][/TD]
[TD]
Grey​
[/TD]
[TD]
Blue​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD][/TD]
[TD]
Grey​
[/TD]
[TD]
Blue​
[/TD]
[TD][/TD]
[TD]
8​
[/TD]
[TD]
16​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
White​
[/TD]
[TD][/TD]
[TD]
15​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
White​
[/TD]
[TD][/TD]
[TD]
30​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
White​
[/TD]
[TD][/TD]
[TD]
50​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD][/TD]
[TD]
Yellow​
[/TD]
[TD]
White​
[/TD]
[TD][/TD]
[TD]
60​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD][/TD]
[TD]
White​
[/TD]
[TD]
Yellow​
[/TD]
[TD][/TD]
[TD]
15​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD][/TD]
[TD]
White​
[/TD]
[TD]
Yellow​
[/TD]
[TD][/TD]
[TD]
30​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD][/TD]
[TD]
White​
[/TD]
[TD]
Yellow​
[/TD]
[TD][/TD]
[TD]
35​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD][/TD]
[TD]
White​
[/TD]
[TD]
Yellow​
[/TD]
[TD][/TD]
[TD]
40​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
Green​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
Green​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
Green​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21​
[/TD]
[TD][/TD]
[TD]
Red​
[/TD]
[TD]
Green​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
25​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
25​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
26​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
26​
[/TD]
[TD][/TD]
[TD]
Green​
[/TD]
[TD]
Red​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
X​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
27​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in J2 copied down
=SUMPRODUCT((ROW(B$2:B$26)>=LARGE((B$2:C$26=H2)*(G$2:G$26<>"X")*ROW(B$2:B$26),4*I2))*(G$2:G$26<>"X")*(B$2:C$26=H2),E$2:F$26)

Hope this helps

M.
Click to expand...

Thank you Marcelo. Thats very interesting how we can get the same result in many different ways.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Disallowing user to change cell color
Replies
5
Views
214
Logit
Logit
K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
295
Cubist
Cubist
S
  • Solved
Five Values that Equal a Sum Value
2
Replies
12
Views
222
ststern45
S
R
  • Question Question
VBA to automate calculation
Replies
1
Views
185
HighAndWilder
H
S
  • Question Question
Help With Calculations
Replies
1
Views
107
ststern45
S

Forum statistics

Threads
1,226,693
Messages
6,192,466
Members
453,726
Latest member
JoeH57

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top