On time performance by date.

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

i have used the following 2 formulas
change range to suite your extended data

I have used to find the months using your text entries of JAN , FEB etc
as they are text this made it a little more difficult and had to use a lookup to change the text into a month number, 1 to 12
(FIND(LOWER(D3),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1))
Then sumproduct by month number

=IF(D3="","",SUMPRODUCT(--(MONTH($B$3:$B$23)=(FIND(LOWER(D3),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)))

this multiplies the number of months only when there is a 1 in column c

=IF(D3="","",SUMPRODUCT(--(MONTH($B$3:$B$23)=(FIND(LOWER(D3),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(($C$3:$C$23)=1)))

Hi. Thank you so much.
I have created the SS and tried to add your formula but I have made a mistake.
I hope someone could take a look for me

where have you added ?

Ok, you may need to explain a little more on the data

So am I looking at the results sheet now ?
column B has the TEXT for each month Jan to Dec

where are the dates to count ? - what sheet and column
and where are the Met list of 1's - what sheet and column is the met flag ? and is it still a 1 - i see ontime as a tick


I have guessed
Dates are in column Q ???
and MET = "ü" in column S ???

anywhere near ?

It’s easy to tell if the large formula is working as the results for JAN will show JOBS = 8 and On time = 4

still dont know what columns to use

as i dont get 8 or 4 ???

can you tell me the columns to use

thanks , that helped a lot

Dates Are in column I and S = 1 = ontime

total jobs =

=IF(B5="","",SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$57)=(FIND(LOWER(B5),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)))

ON TIME
=IF(B5="","",SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$57)=(FIND(LOWER(B5),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*((Jobs!$S$5:$S$57)=1)))

Probably dont need the IF ( B5 =""

total jobs =

=SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$57)=(FIND(LOWER(B5),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1))

ON TIME
=SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$57)=(FIND(LOWER(B5),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*((Jobs!$S$5:$S$57)=1))

so how is a 1 set , is it if column R is 0 or positive ie >0
=IF(OR(R5="",R5<0),"",1)

So if the cell is blank OR if its less than 0 , TRUE , then put a blank
otherwise put a 1 as its 0 or greater in R5

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

you are welcome

Yes, Change this:

=IF(D5="0", "", E5/D5)

to this:

=IF(D5=0, "", E5/D5)

You'll need to do the same for all similar formulae:

=IF(B14="","",IFERROR(\(\SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$57)=(FIND(LOWER(B14),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1))),""))

Oh, come on! Surely you can see from the other solutions you've been given how to deal with it?

=IF(D14="","",D14-E14)

There's nothing that isn't working, just things that need adjusting for the blank cells you want instead of the 0 values you had.

In that case, I’d just live with the 0 values. It’s just aesthetics.

That’s nothing to do with the tweaks I gave you - they were to do with the last three rows. Hopefully Etaf can assist with that.

so month 1 is in an empty / blank cell =date sees this as 1/1/1990

and since the range is I5:I100 - its reporting the blank cells

if range is I5 to I63 - it works

So i need to look at that an exclude blanks, so you can have any range

you nolonger need the IF , as this was for your original sample, i think i mentioned

so to avoid blank cells calculated
for D4 TOTAL JOBS then you can use
=SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$100)=(FIND(LOWER(C4),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(Jobs!$I$5:$I$100<>""))
for E4 ON TIME then you can use
=SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$100)=(FIND(LOWER(C4),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(Jobs!$I$5:$I$100<>"")*(Jobs!$S$5:$S$100=1))

I could understand where you want to show "" instead of 0
you do need to describe what you want a little to help us understand

Thanks etaf. I need it to be flexible so jobs can be added and not mess up the formula just adds to results
I really appreciate your help. I’m not very good at explaining it and the end user is not sure what they want, so I apologise for being vague.

as you can see thats now sorted , so you can extend the rows from 63 to however many you want

also the blanks / zero question - don't understand exactly where, please be specific

how will you know if the result is zero on time ?

so i have added a formula - JUST to the DEC line , see if that works for you

TOTAL CALLS
=IF(SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$100)=(FIND(LOWER(C7),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(Jobs!$I$5:$I$100<>""))=0,"",SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$100)=(FIND(LOWER(C7),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(Jobs!$I$5:$I$100<>"")))
So if total calls = 0 then blank

Now ONTIME
so we dont miss out on zero ontime calls
I added a different test, D will be blank if no calls - so i test to see if D is blank
=IF(D7="","",SUMPRODUCT(--(MONTH(Jobs!$I$5:$I$100)=(FIND(LOWER(C7),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1)*(Jobs!$I$5:$I$100<>"")*(Jobs!$S$5:$S$100=1)))
that way if there are call, and NO ontime , it will still show a zero , otherwise its blank

For G, we can test D again
=IF(D7="", "", E7/D7)
Again , testing the total calls - as it is possible if you actually have zero on time calls, then the % is zero and that should be displayed

attached
JUST changed the formula in D7
so they can be copied , if it works for you

not sure of your formula
=IF(Q5="", "", K5-R5)/10
This is dividing the difference by 10 - not sure how you came to that number, perhaps explain if thats what is needed


To calculate the % - then its the difference between the 2 values - divided by the original value
SO
20 & 10
= 20-10
then divided by the original number = 20
=(20-10)/20 = 0.5 = 50%


=IF(Q5="", "", (K5-R5)/K5)

Sandy - this is a COMPLETELY new query, not a simple follow-on. Please start a new thread with an appropriate title. Thanks.

This thread should be marked as solved now.

do you have a link ?
I looked at your posts on forum, and cannot see a new thread

You must wait for him to respond to your new thread, if he wishes to help you.

Here's a link for anyone interested: https://www.excelfom.com/excel-gener...e-targets.html