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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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

T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
71
Peter_SSs
Peter_SSs
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
247
Moley84
M
A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
144
Abdo
A
F
  • Solved
Sum with some criteria
2
Replies
12
Views
329
Peter_SSs
Peter_SSs
DB73
  • Question Question
calculating percentage based on cell values
Replies
2
Views
114
DB73
DB73

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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