combine IF function and SUM(COUNTIF(INDIRECT)

[yougy]

Hi, All Members.

I want to count how many days people work and filter weekend and holly days and normal days for each emplyee. this is an example of my table:

1.jpg

2.jpg

3.jpg

The problem the code it does not work as expected it counts in linier direction

thank you in advance

I have changed my location into Algeria and here it is the workbook

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[yougy]

Thank you AliGW I will followin the future post. and I am from Algeria

[AliGW]

Thank you. Post the workbook here, plesae - don't open a new thread.

Change your location in your profile from 'system' to 'Algieria'.

[yougy]

I could not upload the workbook here and they did not allow me to link a download link of my workbook

[Glenn Kennedy]

Yes you can... but you do need to follow the instructions.

Unfortunately the attachment icon doesn't work at the moment.

So... for a new thread (1st post), scroll to Manage Attachments

and for a new post in your existing thread click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments.

In both cases, the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window.

When you have finished composing your post, click on Submit Post.

[Glenn Kennedy]

Note: there are file size limits as well (suggested sample size - seeyellow banner - top is 10-20 rows of data). Files > 1Mb must be zipped or saved as xlsb... and are more likely to be ignored as manual checking is MUCH more difficult

[yougy]

I did as you explain

[Glenn Kennedy]

OK. I can see the file.

1. Your formula is rather complicated, I think.

2. However, you did not include any expected results... or any explanation about HOW you derived them.

3. These are simplified versions of your current formulae, in I:

=COUNTIFS($A:$A,$G2,$C:$C,">"&5)

and in H

=COUNTIFS($A:$A,$G2,$C:$C,"<="&5)

So, what ARE your expected results, and why??

[yougy]

Thank you very much Glenn Kennedy, it worked as expected ,but it still need some touches.

Firstly:

The expected result was to count how many times each name appear in range A1:A10 in relation with range C1:C10 to know how many normal days and weekends for each employee. The problem was it counts but if i choose a name from range G in location 5 or more it doesn't count. Your answer as simple but effective it did the requested and counts everything.

I just need some touches in the Holidays Range J1:J10.
It must be related to range B1:B10 for example if Holiday is 02/01/2022 it counts 1 in range B1:B10 for the employee from range A1:A10 that it's name in range G

thank you inadvance

[Glenn Kennedy]

Where is the list of holidays? Please add them and manually add some expected answers and repost the file in THIS thread.

[yougy]

Ok,but you have to put in consideration that holidays can be normal days and can be a weekend so we must mins the number of holiday from the number of normal days if the holiday is a normal day and mins it from the number of weekend if the holiday is a weekend

[Glenn Kennedy]

OK. Change of approach.

=IF(B2="","",IF(AND(ISNUMBER(MATCH(B2,$E$2:$E$10,0)),B2<>""),"H",IF(WEEKDAY(B2)<=5,"N",IF(WEEKDAY(B2)>=6,"W",""))))

to define the day type... and then just use COUNTIFS, like this one:

=COUNTIFS($A:$A,$G2,$C:$C,"H")

see the sheet.

[Glenn Kennedy]

I just noticed that there is a redundant calculation in column C:

=IF(B2="","",IF(ISNUMBER(MATCH(B2,$E$2:$E$10,0)),"H",IF(WEEKDAY(B2)<=5,"N","W")))

Edited by GK at 10:18... another tweak to the formula.

[yougy]

thank you very much worked as expected I will populate it to the days of the month and see the last result.

=IF(B2="";"";IF(AND(ISNUMBER(MATCH(B2;$E$2:$E$10;0));B2<>"");"H";IF(WEEKDAY(B2)<=5;"N";IF(WEEKDAY(B2)>=6;"W";""))))

i understand the second part [;IF(WEEKDAY(B2)<=5;"N";IF(WEEKDAY(B2)>=6;"W";"")))]

but the first part didn't get the idea

[Glenn Kennedy]

Take a look at Post 14... use it, it's simpler to follow and understand!!

=IF(B2="","",IF(ISNUMBER(MATCH(B2,$E$2:$E$10,0)),"H",IF(WEEKDAY(B2)<=5,"N","W")))

Black - if B2 is blank, return blank.

red: looks to se if there is an exact match between B2 and any of the holidays. It will return the row number from E2:E10, or an error

Orange: If it's a number... it's a holiday, so return H

Cyan: if the weekday is <=5... it's a normal day, so return N

Blue: otherwise return W (as there are no other possibilities).

[yougy]

Thanx very much