N/A to 0 Solution

Originally Posted by NBVC

Change formula to:

=IFERROR(INDEX($Q$2:$Q$5,MATCH(B2,$P$2:$P$5,0)),0)

Thankyou, that worked a treat.

I do have one more question regarding this sheet.

If I were to add an input within the drop down boxes (SK & UL) and wanted there figures to individually add up in there own cells(SK=SICK DAYS & SICK DAYS HIDDEN), (UL=UNPAID DAYS, UNPAID DAYS HIDDEN) without being subtracted from 'REMAINING' , 'DAYS OFF (HIDDEN)' & DAYS OFF.

How could I do this?

I have attached the updated sheet.

Thanks again

Craig

Originally Posted by NBVC

I am not sure I follow...

Can you please elaborate with sample entries and desired results?

Thanks NBVC for the prompt replies.

Please find the file attached with a description of what I require with some colours that should help.



Craig

Originally Posted by NBVC

Not exactly sure but is it?

In K2:

=COUNTIF(B2:B10,"SK")

in L2:

=COUNTIF(B2:B10,"UL")

then in H2:

=(F2-J2+SUM(K2:L2))

Thanks NBVC, those formulas worked.

However another problem has arose. If I were to put my drop down boxes in another column, I currently cant get the range for 'SICK DAYS' total & 'UNPAID DAYS' total to work with the new column.

Is this possible?

File Attached.

Originally Posted by NBVC

with COUNTIF() you can expand the range in both directions...

so you can change your formulas in K2 and L2 to:

=COUNTIF(B2:D10,"SK")

and

=COUNTIF(B2:D10,"UL")

so now it will count all the matches in columns B and D

Thanks NBVC, I have now transferred all of these formulas to my final spreadsheet and successfully got it working.

Thanks for your help.


Craig