Help with formulas

New Contributor

Hi

I have a formula, in a document which I did not create (a leave chart) which for some reason, takes the sickness off the leave totals, which I do not want so I want to exclude this from the leave formulas.  However, I do not understand the formula (I am a basic user) enough to be able to make the change I want.  Can anyone help me understand this formula below

The formulas is....

=SUMPRODUCT((OFFSET($A8,0,1,1,372)<>"")*(IF(OFFSET($A8,0,1,1,372)=$NW$13,0.5,IF(OFFSET($A8,0,1,1,372)=$NW$14,0.5,1))*(OFFSET($A$3,0,1,1,372))))

I have attached the document in question - if you type an S - it adds it to the leave totals.

Please help  

 

7 Replies

@nicolam1005 

See the attached version.

@Hans Vogelaar 

 

Thank you, you are a genius and it works on your document, but it will not allow me to copy it over to my document for some reason?

 

Also the H1 and H2 function adds 1 in the end boxes rather than a 0.5 or half.

 

Any ideas.  Sorry to ask more questions, but I sent the original document and not the one I had already adapted to the business

@nicolam1005 

In the sample workbook, columns NT and NU simply count the number of cells with H1 and H2, respectively.

In columns NJ and NK, these count for 0.5.

So for example if there are three cells with H1 in row 8 (and no other cells with codes), cell NT8 will show 3, but cells NJ8 and NK8 will show 1.5.

S0875.png

It's impossible for me to know why it doesn't work in your own workbook without seeing (a copy of) it.

@Hans Vogelaar 

Dear Hans

Apologies for the delay in responding - I couldn't get back to this.
I should have attached the document I actually use, which would make more sense.
I thought I would be able to copy over what you did to sort out the issue with the sickness days, but it does not seem to copy over. Any further suggestions. This is the actual chart I current use.
Kind regards
Nicola Morris

@nicolam1005 

See the attached version. I had to save it under another name since your workbook opens read-only.

Hans, you are a legend, how you did this I have no idea, I fumbled my way through for about 3 hours. Thank you for sorting it out.
Kind regards
Nicola

@nicolam1005 

I copied the formulas from my previous attachment and adjusted them for the slight differences in layout (codes in column NW instead of NX, and H1/H2 in row 13/14 instead of 16/17).