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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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

K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
162
Cubist
Cubist
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
72
Peter_SSs
Peter_SSs
B
  • Question Question
Even distribution of hours/value across multiple rows
Replies
7
Views
101
BGwayne
B
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
305
Moley84
M
A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
192
Abdo
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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