COUNT Functions

Hi,

So I have a spreadsheet that covers a years worth of shifts. I've got a COUNTA formula that adds up the amount of weekends worked for each person that is as follows.

=COUNTA(E5:E6,E12:E13,E19:E20,E26:E27,E33:E34,E40:E41,E47:E48,E54:E55,E61:E62,E68:E69,E75:E76,E82:E83,E89:E90,E96:E97,E103:E104,E110:E111,E117:E118,E124:E125,E131:E132,E138:E139,E145:E146,E152:E153,E159:E160,E166:E167,E173:E174,E180:E181,E187:E188,E194:E195,E201:E202,E208:E209,E215:E216,E222:E223,E229:E230,E236:E237,E243:E244,E250:E251,E257:E258,E264:E265,E271:E272,E278:E279,E285:E286,E292:E293,E299:E300,E306:E307,E313:E314,E320:E321,E327:E328,E334:E335,E341:E342,E348:E349,E355:E356,E362:E363)

Probably not the ideal way to do this I know, but the problem is when someone types "Annual Leave", "A/L" or "N/A" it obviously adds it to the working weekend count. Is there a solution to negate this?

Sorry for the delayed response, I have attached small workbook as an example.

hi there, after check the consult I allow to propose this possible solution to copy and drag to right from D35:

Please Login or Register  to view this content.

I hope it results useful.

Originally Posted by HansDouwe

Note: This function does exactly the same as your COUNTA function:

Formula:

Please Login or Register  to view this content.

Perhaps other formulas are possible in your sheet, but I would have to see these first.

For your question, how do you prevent weekend days from being counted incorrectly, we need to see an example workbook.
A rule must then be devised for Excel how to distinguish between a weekend day that is worked and a weekend day that is not worked.

This worked really well, thank you.

Now is there a way to differentiate that when I type 'N/A' or 'A/L' in the weekend boxes they don't add to the working weekend table. I have attached an example book. It currently has the old formula in there.

Or try this:

=SUMPRODUCT((WEEKDAY($C5:$C32,2)>5)*(D5:D32<>"")*(D5:D32<>"N/A")*((D5:D32<>"A/L")))