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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Please try to post Excel readable data, not an image or a picture as these require retyping what they show.

Control+shift+enter, not just enter:

=AVERAGE(IF(ROW(sumrange)>=LARGE(IF(colorange=color,IF(ISNUMBER(sumrange),ROW(sumrange))),MIN(4,COUNTIFS(colorrange,color))),IF(colorrange=color,IF(ISNUMBER(sumrange),sumrange))))
 
Upvote 0
Aladin Akyurek said:
Please try to post Excel readable data, not an image or a picture as these require retyping what they show.

Control+shift+enter, not just enter:

=AVERAGE(IF(ROW(sumrange)>=LARGE(IF(colorange=color,IF(ISNUMBER(sumrange),ROW(sumrange))),MIN(4,COUNTIFS(colorrange,color))),IF(colorrange=color,IF(ISNUMBER(sumrange),sumrange))))
Click to expand...

Thanks for replying.
I couldn't do it. I got an error (#VALUE, #NUM )
Where Should I type the parameter I2 on the formula (Value)
=AVERAGE(IF(ROW(E2:F26)>=LARGE(IF(B2:C26=I1,IF(ISNUMBER(E2:F26),ROW(E2:F26))),MIN(4,COUNTIFS(B2:C26,I1))),IF(B2:C26=I1,IF(ISNUMBER(E2:F26),E2:F26))))


[TABLE="width: 477"]
<tbody>[TR]
[TD]Color A[/TD]
[TD]Color B[/TD]
[TD][/TD]
[TD]S1[/TD]
[TD]S2[/TD]
[TD][/TD]
[TD]Color[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Values[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]SUM[/TD]
[TD]69[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grey[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grey[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grey[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grey[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]White[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]White[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]White[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]White[/TD]
[TD][/TD]
[TD]60[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD]35[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]X[/TD]
[TD]NOT SUM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

2j3KNJX.png
 
Last edited:
Upvote 0
Firstly, replace AVERAGE with SUM.

Control+shift+enter: Press down the control and
the shift keys at the same time while you hit the enter key.
If done properly, Excel itself puts a pair of { of }
around the formula in recognition.
 
Upvote 0
Aladin Akyurek said:
Firstly, replace AVERAGE with SUM.

Control+shift+enter: Press down the control and
the shift keys at the same time while you hit the enter key.
If done properly, Excel itself puts a pair of { of }
around the formula in recognition.
Click to expand...

Thanks mate.
Its working but only when I type value = 1, if I type 2,3,4,5, I get the same result.

LAOOPey.png


Same result
Gmq5t1T.png
 
Last edited:
Upvote 0
tennis07 said:
Thanks mate.
Its working but only when I type value = 1, if I type 2,3,4,5, I get the same result.
Click to expand...

It looks like Aladin forgot to specify the 'data' input cell * 4...

Insert the datacell* just before the 4, as seen in this formula.

=SUM(IF(ROW(sumrange)>=LARGE(IF(colorange=color,IF(ISNUMBER(sumrange),ROW(sumrange))),MIN(datacell*4,COUNTIFS(colorrange,color))),IF(colorrange=color,I F(ISNUMBER(sumrange),sumrange))))
 
Upvote 0
dave2018 said:
It looks like Aladin forgot to specify the 'data' input cell * 4...

Insert the datacell* just before the 4, as seen in this formula.

=SUM(IF(ROW(sumrange)>=LARGE(IF(colorange=color,IF(ISNUMBER(sumrange),ROW(sumrange))),MIN(datacell*4,COUNTIFS(colorrange,color))),IF(colorrange=color,I F(ISNUMBER(sumrange),sumrange))))
Click to expand...

Thanks for helping.

It doesn't accept that.
 
Upvote 0
tennis07 said:
=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))))

Return: #VALUE !
Click to expand...

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 :(
 
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
161
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,175
Members
453,021
Latest member
Justyna P

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