Stop counter and add to other cell

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Then you will have to try agsin to explain what you want. Your example was simply 6 digits from 0 to 5.... No explanation at all.

How we were we expected to guess this from your first description???!!!
Try this...

NL8: =MIN(SUM(NJ8,NK8),4)

NM8: =MIN(SUMPRODUCT((OFFSET($A6,0,31*($A$3-1)+1,1,31)=NP17)+(OFFSET($A6,0,31*($A$3-1)+1,1,31)=NP18)),8)

NN8: =SUMPRODUCT((OFFSET($A6,0,31*($A$3-1)+1,1,31)=NP17)+(OFFSET($A6,0,31*($A$3-1)+1,1,31)=NP18))-NM8

The formulae in NJ8 and NK8, along with these formulae, are all ordinary formula, no need for CSE, I think...

@FredyHar (and also taken to the attention of Admins/Moderators) and is it the same user as topmayra here: https://www.excelforum.com/excel-for...h-out-vbs.html

@Glenn Kennedy

YOUR ANSWER is helpfull my friend, thnak you. But if i delete all W then Day Off Year has value 2 the correct value is 0

how to fix this? and if i change month then look Day Off Year change with out W....

The whole thing seems over complicated. However, I do not intend to spend ages reading any of your previous threads on this subject. Please explain clearly, here, what EACH of the 5 formulae are supposed to do, for a couple of example rows.... what the expected results are.

OK first if add W to orange cell (hollidays list day) then Day Off Year +1 (I DON'T KHOW HOW TO CEATE THAT)

NM8: =MIN(SUMPRODUCT((OFFSET($A6,0,31*($A$3-1)+1,1,31)=NP17)+(OFFSET($A6,0,31*($A$3-1)+1,1,31)=NP18)),8) <--delete this function.

second : i change your

Formula:

Please Login or Register  to view this content.

with this

Formula:

Please Login or Register  to view this content.

to check 2 parametres..

Now i upload new edit example file and check e.g May 2018 he's got : SUM Sat/Sun = 4 and 1 Holliday (Orange cell) the Day Off Year cell it must be 2. 1 because has 5 Sat and Sun "W" and 1 because has one "W" in Holidays.

if you go to june 2018 Total Sat/Sun Month is > 8, THEN SUM Sat/Sun go max 5, and Day Off Year cell it must be 2 again... check my new file

What new file?

SORRY my mistake not upload file. look now... i add this function to Day Off Year :

Formula:

Please Login or Register  to view this content.

for now July 2018 work great and is what i want, the correct answer is 4. but if i change month ang go to August 2018 then Day Off Year go to 9! and no keep previus July 2018 count (4) and start from 4 count for august...


And last problem is with Employee Holidays (EH) if add this symbol on Sat Sun no count if i add to working days start count -1! i do not want tocount this days! or if i add other latter in working days count go to -1... this is bug

I'm beginning to see what you want. But... A couple of posts ago I asked you to explain what eaxh of the 5 formulae in NJ8 to NN8 are meant to be counting. Please DO NOT describe the formulae that are already there. I can see the formulae. What I do not understand is what you THINK that they are doing.

Is NJ8 the count of Saturdays in the chosen month... or something different?

Is NK8 the count of Sundays in the chosen month... or something different?

What is NL8 meant to be a total of?

What is NM8 meant to be? How is it different from NL8?

OK. We are not there yet, but this is at least part of it.

Your sheet set up was very confusing. BUT the way it is set up has allowed me to exploit much more simple formulae in giving you the desired result.

NJ, NK and NN are correct (I think).

NL MIGHT be correct. It is the number of Sat/Sun worked to a maximum of 4 or 5 (depending on no of Sat/Sun in the month). The balance is shown in NN.

NM. I still have no idea what this is supposed to be doing....

it might be easier to follow if you entered a few employees for any one month and gave the expected results....

NN and NL are array formulae and need to be set using CTRL-SHIFT-ENTER

oh!..You are very smart my friend... i edit your function to count R latter with this :

Formula:

Please Login or Register  to view this content.

but when I go to the next month the prices of the previous month are gone... can i keep this values and start next month count from there? Also if add W on orange cell no count why? if work holiday day i give one day off extra

Forget your modification, PLEASE. I can see that it will NOT work. Did my formula work? In which circumstances did it not work?

For the LAST time, provide sample inputs and expected results.
You MUST provide samples and expected answers. I WILL NOT play guessong games forever.

We have a major problem. The way your sheet is set up is that the days and dates are ONLY present on the selected (visible) month. On the other (hidden) months these cells are blank. Also, your data are arranged in columns, not in rows, so Excel functions like SUBTOTAL do not work. In my opinion, you need a major re-think about this sheet ... creating a continuous list of dates/days instead of the current system when they exist only when the month is selected.

Maybe so... but ultimately, it might be worth it. Does this look like a possibility? It is arranged witht he data vertically, not horizontally. NOW, all data can be kept when filters are applied.... or only visible data are counted, depending on what is needed.

Could you live with something like this??