Excluding certain columns from Countif

[Jelsey]

Hello All,
I am looking for help with a formula. I have arranged a horizontal calendar and added a Countif formula at the end to total days that were missed from work. The formula countif formula is counting the cells that have 1 listed in them. However, i want to exclude the columns that have weekend dates. There are numbers in them but I only want to count business days.


Is there a way to exclude those columns? Or a better formula I should be using?

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Jelsey]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Of course. This is just a snit of the table. Each cell under a date is populated with .5 (half day off) 1 (full day off). I have table set to change color based on the value entered. 1 = light green. (First picture)

I need to count all the "1" in the row. which I used the formula Countif (Second picture) However, I need the Countif to exclude the columns that are weekends. Which in the picture would be Column NA and NB. (and 51 other columns that are weekend dates)

Is there a formula or combination of formulas that could help? Or maybe I'm using the wrong one.
Ultimately I want to use the weekend column to total off days for the week. and have the Grand total at the end. as shown above.

 

[Fluff]

How about

=COUNT(FILTER(B3:NB3,(B3:NB3=1)*(WEEKDAY(B1:NB1,2)<6)))

 

[Jelsey]

HOLY ****!! THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU

I been struggling with this for almost 2 days. I appreciate your help sooo much. Could I ask for help with one more formula. As I mentioned above I would like to use the weekend columns to total offs per week. However I want it to be cumulative. So in column H, would have the off days totaled from column C - G. and then in Column O would have the total days off from Column C - D and J - N, and column V would have the total days Off from Column C - D and J - N and Q - U (and so on). What would be a good approach for that?

 

[Fluff]

As that is a significantly different question it needs a new thread. Thanks

 

[Jelsey]

As that is a significantly different question it needs a new thread. Thanks

I understand. thank you once again and I will submit another thread.

 

[Fluff]

Glad to help & thanks for the feedback.