**Question description:**

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

I have a table showing month, agent names, and scores. What I want is calculate scores which are less than 98% from last 5 months for each agent. If the agent has scores for only past 4 months and past 5th month was empty, the formula should look for score before the 5th month to count only the last 5 columns with values

Solved by I. Q. in 51 mins

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

Excelchat Expert
21/02/2018 - 09:55

hi welcome to got it pro

User
21/02/2018 - 09:55

Hi I have added the table

Excelchat Expert
21/02/2018 - 09:56

sure thanks

Excelchat Expert
21/02/2018 - 09:56

let me have a look pls

User
21/02/2018 - 09:56

sure

User
21/02/2018 - 09:57

In column L (highlighted), I want to count for no. of times the agent scored below 98% from past 5 months. If new month is added, the count should be still from past 5 months

Excelchat Expert
21/02/2018 - 09:57

yes pls

User
21/02/2018 - 09:58

Moreover, the count should ignore the blank cells

User
21/02/2018 - 09:58

count only past 5 weeks below 98% where we have scores

Excelchat Expert
21/02/2018 - 09:58

i got you give me a moment pls

Excelchat Expert
21/02/2018 - 09:58

i wil work for u

User
21/02/2018 - 09:58

sure

User
21/02/2018 - 09:58

thanks

Excelchat Expert
21/02/2018 - 09:59

i have downloaded excel sheet,..

Excelchat Expert
21/02/2018 - 09:59

thanks

Excelchat Expert
21/02/2018 - 10:04

hi dear i just did working for u,..are you able to see that

Excelchat Expert
21/02/2018 - 10:04

pls

User
21/02/2018 - 10:05

just a min plz

Excelchat Expert
21/02/2018 - 10:05

sure

User
21/02/2018 - 10:06

could u plz add the count to the last column

Excelchat Expert
21/02/2018 - 10:06

yes pls

User
21/02/2018 - 10:06

After Feb. I need to check if I make a cell blank, will it go and count the next column

Excelchat Expert
21/02/2018 - 10:07

yes pls it will go

User
21/02/2018 - 10:07

Now, the count shows '4'as the answers but it should show 3 as there are 3 values less than 98% in past 5 months

Excelchat Expert
21/02/2018 - 10:07

do u want me to create a templete for u

Excelchat Expert
21/02/2018 - 10:08

i will do it for u

User
21/02/2018 - 10:09

thanks

User
21/02/2018 - 10:09

but the answers are incorrect for now.

Excelchat Expert
21/02/2018 - 10:09

they will be right

User
21/02/2018 - 10:09

I mean when u added the count column after 'FEb', it is showing incorrect values

Excelchat Expert
21/02/2018 - 10:09

yes i am fixing it

User
21/02/2018 - 10:10

sure, thanks. Also, the formula should only count last 5 values if all of them have data. If all the last 5 values do not have data, then formula should extend to the last 5 values having data

User
21/02/2018 - 10:10

In short, it should ignore the blank cells

Excelchat Expert
21/02/2018 - 10:10

Yes pls

User
21/02/2018 - 10:11

Also, everytime, a new column is added 'March', April, etc, the formula should change to count to new last 5 columns

Excelchat Expert
21/02/2018 - 10:11

Yes Pls

User
21/02/2018 - 10:11

thanks

Excelchat Expert
21/02/2018 - 10:12

can i do working on my sheet Pls

Excelchat Expert
21/02/2018 - 10:12

and then upload

Excelchat Expert
21/02/2018 - 10:12

so that you can down load

User
21/02/2018 - 10:12

sure, thanks

Excelchat Expert
21/02/2018 - 10:13

did u just saw week 2 working

User
21/02/2018 - 10:14

no, it shows incorrect results

Excelchat Expert
21/02/2018 - 10:15

are we talking week 2

User
21/02/2018 - 10:15

yes

User
21/02/2018 - 10:15

Cell N7 shows value as 1 but there are 3 values which are less than 98% from past 5 weeks

User
21/02/2018 - 10:16

Similarly all other cells except N2 also show incorrect values

Excelchat Expert
21/02/2018 - 10:17

dear let me explain you,..

User
21/02/2018 - 10:18

sure

Excelchat Expert
21/02/2018 - 10:18

week 2 is taking data from Jan to May

Excelchat Expert
21/02/2018 - 10:18

watch this

Excelchat Expert
21/02/2018 - 10:18

5 Month

Excelchat Expert
21/02/2018 - 10:20

in week 2 Just press F2

User
21/02/2018 - 10:20

is it the standard countif formula or anything other formula is added?

Excelchat Expert
21/02/2018 - 10:21

nope for numeric value = countif is the Perfects

User
21/02/2018 - 10:22

but when new column is added, it is not taking the last 5 columns.

User
21/02/2018 - 10:22

the formula need to shift to last 5 columns everytime a month is added

User
21/02/2018 - 10:22

standard countf will not shift the range and will work on fixed table and not on dynamic table

Excelchat Expert
21/02/2018 - 10:22

yes for that you have to create a new column and copy paste every five month

Excelchat Expert
21/02/2018 - 10:23

or you can also do is create a templete for 1 year

User
21/02/2018 - 10:23

that I already know. I was looking for a formula which counts the last 5 columns. I heard about offset formula and that can work

User
21/02/2018 - 10:23

if you can check that - combine offset formula with countif

Excelchat Expert
21/02/2018 - 10:24

sure givem me a momentspls

Excelchat Expert
21/02/2018 - 10:26

give me a moment pls

Excelchat Expert
21/02/2018 - 10:26

al most done

User
21/02/2018 - 10:27

please make sure that it only counts the last 5 cells which have data and should extend to 6th or 7th cell if 1 or 2 cells in last 5 cells are blank

Excelchat Expert
21/02/2018 - 10:27

sure

User
21/02/2018 - 10:34

will u be able to do it. I think it will be in best interest of our time that we disconnect the chat if you are not well versed with offset and countif functions together

User
21/02/2018 - 10:34

Sorry for thinking out loud

Excelchat Expert
21/02/2018 - 10:35

dear,. i can add the Offset for mulae however that will not resolve your purropse

User
21/02/2018 - 10:36

ok, so will u able to provide an alternative which serves the purpose

Excelchat Expert
21/02/2018 - 10:38

looking as per this report and data that u r looking at ,..you req a templete

Excelchat Expert
21/02/2018 - 10:38

for no of weeks you reuire

Excelchat Expert
21/02/2018 - 10:38

or else Macro will be and alternative for u

User
21/02/2018 - 10:39

I would prefer a formula but if that doesn't work, macro will work too

Excelchat Expert
21/02/2018 - 10:40

nope in macro,. you can give condition,..

User
21/02/2018 - 10:41

I don't have any experience with macro so that would be tough to alter or edit, if required in future

Excelchat Expert
21/02/2018 - 10:41

on got it pro you can get MACRO expert as well

Excelchat Expert
21/02/2018 - 10:41

ohh i see dear

Excelchat Expert
21/02/2018 - 10:42

for the mean time you an try using the count if function,..that will resolve ur purpose

User
21/02/2018 - 10:43

but I need to add it again every month. Moreover, I want to count the last 5 values but countif will count not extend the range if any of the last 5 cells do not have a value

Excelchat Expert
21/02/2018 - 10:44

yes agreed,..

Excelchat Expert
21/02/2018 - 10:44

however all u will require is few copy and paste and chnage of rages

User
21/02/2018 - 10:45

k, I will try other alternatives. thanks

Excelchat Expert
21/02/2018 - 10:45

sure good luck with ur work,.. TC

Excelchat Expert
21/02/2018 - 10:45

thanks for contacting got it pro

Excelchat Expert
21/02/2018 - 10:45

bye

User
21/02/2018 - 10:46

bye

**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.*