Forum Discussion
Help with formulas
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 🙂
14 Replies
See the attached version.
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
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.
It's impossible for me to know why it doesn't work in your own workbook without seeing (a copy of) it.
