**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need to count scores less than 98 from last 5 months. After each month, a new month is added so it is a dynamic table range. Could you please suggest me an offset formula, not so complicated one

Solved by K. L. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
20/02/2018 - 10:42

Hi

User
20/02/2018 - 10:42

need help with offset formula. Can u check my earlier communication and let me know if it was resolved

Excelchat Expert
20/02/2018 - 10:43

Hi..

User
20/02/2018 - 10:43

email address zorawar81@yahoo.co.in

Excelchat Expert
20/02/2018 - 10:43

We do not send out emails

Excelchat Expert
20/02/2018 - 10:43

we will sort it out here right now..

Excelchat Expert
20/02/2018 - 10:43

Do you have a excel

Excelchat Expert
20/02/2018 - 10:43

?

User
20/02/2018 - 10:43

so the inquiry under that email is not resolved, I would presume

User
20/02/2018 - 10:44

The table I've pasted shows months in one row and scores in other row. I need an offset formula to count how many cells from past 5 cells are below 98%

Excelchat Expert
20/02/2018 - 10:45

If you had come earlier youcan contact the support to see if the other expert found a solution

User
20/02/2018 - 10:45

I tried something like the below but it doesn't work

User
20/02/2018 - 10:45

=OFFSET($D$17,0,1,COUNTIF(D17:M17, "<0.98"),-5)

Excelchat Expert
20/02/2018 - 10:46

You need to know how many cel'l's in the matix are below 98% right?

User
20/02/2018 - 10:46

This is a dynamic table and a new month will be added every month so the last 5 columns would change every month

User
20/02/2018 - 10:46

yes, how many cells below 98%

User
20/02/2018 - 10:46

but normal countif won't work as the table will add new column every month

User
20/02/2018 - 10:47

it needs to be mix of offset and countif

Excelchat Expert
20/02/2018 - 10:47

and you would need only the last 5 month's not the previous?

User
20/02/2018 - 10:47

yes

Excelchat Expert
20/02/2018 - 10:47

give me a min

User
20/02/2018 - 10:47

sure, thanks for ur time

Excelchat Expert
20/02/2018 - 10:49

just checking so next when Aug is added you need the formula to work for aug,july,june,may n april right?

User
20/02/2018 - 10:49

correct

Excelchat Expert
20/02/2018 - 11:00

the offset will not work.. looking into other formulas

Excelchat Expert
20/02/2018 - 11:03

hELLO ARE YOU THERE

Excelchat Expert
20/02/2018 - 11:03

Sorry caps lock on

User
20/02/2018 - 11:03

yes

User
20/02/2018 - 11:03

ok

Excelchat Expert
20/02/2018 - 11:04

few more min

User
20/02/2018 - 11:04

sure

Excelchat Expert
20/02/2018 - 11:17

[Uploaded an Excel file]

Excelchat Expert
20/02/2018 - 11:17

hi..

Excelchat Expert
20/02/2018 - 11:17

you there

Excelchat Expert
20/02/2018 - 11:17

its a simple formula =COUNTIF(LastMonth,"<98%")

Excelchat Expert
20/02/2018 - 11:18

ut created a namerange for the calculation purpose

Excelchat Expert
20/02/2018 - 11:18

Hello

Excelchat Expert
20/02/2018 - 11:18

Hi are you there

Excelchat Expert
20/02/2018 - 11:19

Hello

Excelchat Expert
20/02/2018 - 11:19

the formula is in K13

Excelchat Expert
20/02/2018 - 11:19

If you are there please respond

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*