Count additional days

Hello everybody
Before mentioning my question,I'd like to thank all members in this forum.you helped me alot in my questions.
The question:
*Column "A" contains rest type for every employee
*"AK3" contains Rest days scehdule
*I want to count the number of attendance "refered to as 8" in the rest days of each employees ,in another word additional days
*I prefer using formula rather than VBA or udf
*I want the formula to be in column "AI"
I hope you help me

You misunderstood me
These are 2 examples,last row in my data is 15.
I want the formula to be in column "AI"
I don't want to count on helper columns.
Note:
My original file contains 150 employees
I hope you help me

I edited the data as you requested.find the attachment.

Originally Posted by nflsales

AI4

Please Login or Register  to view this content.

TRY THIS AND COPY TOWARDS DOWN

Thank you sir for your help
1-Sumproduct works but countifs didn't work.can you try to correct it?
2- can you create a function for my first file??as in my second file I changed the file as I added the 31 days in rest days schedule
I wish you help me

COUNTIFS formula produced same result as SUMPRODUCT when I tested it on you file from post #7.

Can you help me in the file from post1#

Try

Example 1

=SUMPRODUCT(($D$29:$AH$29=8)*($D$28:$AH$28=TRANSPOSE($AK$29:$AR$29)))

Example 2

=SUMPRODUCT(($D$37:$AH$37=8)*($D$36:$AH$36=TRANSPOSE($AK$37:$AR$37)))


Enter both with Ctrl+Shift+Enter

Try

In AI4

=SUMPRODUCT(($C4:$AG4=8)*($C$3:$AG$3=TRANSPOSE(INDIRECT($A4))))

Enter with Ctrl+Shift+Enter

I create a named range for each "Rest Type" which are referenced via the INDIRECT formula,

Originally Posted by nflsales

please attach fresh excel file with expected result with details how you arrived it

Thank you all for your interest and quick reply
The formula you have already created to calculate the count of additional days.
I want now a formula to write the dates of "additional dates" in column AI.
Find the attachment -column AI

Please Login or Register  to view this content.

=add_Dates(indirect(a4))

See column AK

NOTE: I have changed days in row 3 to be format "00" so entered as numbers, and same for "Rest Days"

Avoid using TEXT for numeric data as it often results in problems.

Thank you sir for your reply.
1- I don't like UDF because it doesnot recalculate well
I have a workbook with multiple sheets,when I edit some data in another sheet,and come back to the sheet that contains the UDF ,I found its result gives error and changes.
Or when I work in another workbook this happens.
I think this happens because the formula take the range only without its current workbook & sheet.
Can you make the UDF take the workbook and sheet with range??
2- or can you use normal formulas??

The scope of the named range is WORKBOOK so using the UDF in many sheets is OK BUT ensure there only ONE version of the "Rest Day" table. Placing the table in a separate is a better solution.

See the attached which has a copy of "Data" ("Data (2)" without the table (still in "DATA")

Otherwise you are left with a complex formula as per post NFLSALES.

Thank you Mr nflsales for your reply
1- can the UDF take workbook&sheet& range?
2-your formula is very big.see the attached,I sumarized the rest days schedule.can you adjust the formula,I think it will be shorter now?

Function only works in workbook in which it resides unless you create a PERSONAL.XLSB file and place UDF here.


https://support.office.com/en-gb/art...0-6e4c3f5ee566

Originally Posted by JohnTopley

Function only works in workbook in which it resides unless you create a PERSONAL.XLSB file and place UDF here.


https://support.office.com/en-gb/art...0-6e4c3f5ee566

I don't want the formula to get data from another workbook.
I want it to get the activeworkbook&active sheet

I want it to get the activeworkbook&active sheet

.. so you already have that solution.

You appear not to understand how this works.

But your udf lacks dealing with active workbook.Try working in any another workbook and return to my workbook,you will find that your udf return error or "".
I hope you ubderstand this.

If the UDF (NOT DATA) was placed in a PERSONAL.XLSB workbook, then the UDF is available to ALL workbooks.

If you don't want to pursue this route then you will have rely on someone producing a manageable formula.

Any reason why you have multiple workbooks rather than multiple sheets in one workbook?

1-the UDF is placed in one file that doesn't take any data from any file else and has no relation to my file.
2-Try working in any other file-that has no relation to my file
3- return to my file,you will find that your udf return error or "".
I hope you ubderstand this.

Please please please please
it is the same problem in the Link.
Why you are insisting that I want to use the formula in multiple workbooks??

It is not the same problem (in my view) : if you want to take it further start a new thread on the VBA macro forum where you will find people with a greater knowledge of VBA than myself. I have it working on multiple workbooks using the approach I suggested.

I am not insisting anything re the formula: you "requested" a formula solution which has been provided.

I repeat my comment about having multiple workbooks and the maintenance required to keep them aligned.

Originally Posted by nflsales

AI4

Please Login or Register  to view this content.

AJ=

Please Login or Register  to view this content.

IT IS BETTER TO USE @JohnTopley VBA Solution
the above formula is looks like a lengthy and it is limited to 8 dates, you can extend by modifying the formula

Thank you Mr nflsales for your reply
your formula is very big.see the attached,I sumarized the rest days schedule.can you adjust the formula,I think it will be shorter now?

I would be most surprised if your new proposal results in a simpler formula as it has to "decode" the text string into the component numbers.

You'll be happy if I am proved wrong!

Is the following acceptable using Conditional Formatting:

CF rule

=AND(MATCH(DAY(C$3),INDIRECT($A4),0),C4=8)

Colour RED in attached sample.

Originally Posted by JohnTopley

Is the following acceptable using Conditional Formatting:

CF rule

=AND(MATCH(DAY(C$3),INDIRECT($A4),0),C4=8)

Colour RED in attached sample.

Thank you Mr. JohnTopley for your conditional formatting!!
But I want to adjust the substitute formula that Mr. nflsales created before as I combined the rest days into one cell.

I am sure if Siva (Mr Nflsales) could have devised a simpler formula he would have done so, as he is very competent at creating complex formulae.

As I stated earlier, I cannot see the change you propose making thinks easier.

IT IS BETTER TO USE @JohnTopley VBA Solution
the above formula is looks like a lengthy and it is limited to 8 dates, you can extend by modifying the formula

from Siva

Ok thanks I don`t want anything else