# Conditional Rolling Data using OFFSET or INDEX (or anything else)

1. ## Conditional Rolling Data using OFFSET or INDEX (or anything else)

Hey,

Not sure if this is possible, but it'd be greatly helpful if it was.

I am looking for a function (OFFSET or INDEX seem the best bet) that only counts the last n instances of an event if it meets certain criteria.

For Example: Say Column A contains "text" ("Y" and "Z") and Column B contains a value. I'd like a formula that sums the last n instances that Column A contains "Y".

Sample:

A B
Y 18
Z 23
Z 16
Y 18
Y 21
Y 24
Z 27
Z 13
Y 19
Z 15

Say in this example I want the last 3 instances of Y (19+24+21=64) or Z (15+13+27=55), but for it to update when new values of each are added. So, before the final "Y" value was added Y (24+21+18=63).

Any help would be appreciated.

2. ## Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

I think this may work... it is an ARRAY formula, meaning you should select a cell, click in the formula bar, and paste the following formula. Instead of hitting ENTER, you have to press CTRL + SHIFT + ENTER to apply it.
Formula:
`Please Login or Register  to view this content.`
Of course, you will need to make the range longer than you would ever need to go, say A1:A500, or something like that, so it looks in those cells for new entries.

- Moo

3. ## Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

From C2 then drag down:

=IFERROR(SUMPRODUCT(--(IF(\$A\$1:\$A1=\$A2,ROW(\$A\$1:\$A1),0)>=LARGE(IF(\$A\$1:\$A1=\$A2,ROW(\$A\$1:\$A1),""),3))*\$B\$1:\$B1)," less than 3 of"&\$A2)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

4. ## Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

here you are
you can select the Y ore Z from the list

Feel free to find more tutorials on my Excel Blog.

5. ## Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

Assumes there will always be 3 instances.

Data Range
 A B C D E 1 ------ ------ ------ ------ ------ 2 Y 18 Y 64 3 Z 23 Z 55 4 Z 16 5 Y 18 6 Y 21 7 Y 24 8 Z 27 9 Z 13 10 Y 19 11 Z 15 12

This array formula** entered in E2 and copied down:

=SUM(LOOKUP(LARGE((A\$2:A\$20=D2)*ROW(A\$2:A\$20),{1,2,3}),ROW(A\$2:A\$20),B\$2:B\$20))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

6. ## Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

Thank You bsuperiorsystem,

This works perfectly. One question, is there any way to make it so that if Column A contains a value ("Y") and Column B is blank it does not count that value? Currently, it returns an N/A error (which makes sense).

I think I can figure it out or can work around it, but if you know offhand it'd be greatly appreciated.

Thanks again.

7. ## Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

Moo,

Thank you, this works as well, and may be easier for me to work with. One question similar to my previous post. When a there's a value in Column A but not in Column B any way to make it so it does not count (currently it counts as zero which makes sense).

I can probably play around with some version of IF(NOT(ISBLANK(*))) but if you know off hand it'd be a great help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1