Numbers formula for counting only weekends and a second criteria

Question

Level 1

8 points

Numbers formula for counting only weekends and a second criteria

Hi,

I have a spreadsheet that I use to calculate my pay at work. I get paid a premium when I work a weekend shift. I have included a screenshot of the spreadsheet. What I am looking to do is in cell AI7. Count the number of Saturdays or Sundays in C6:AG6 that also have a 1 or a 2 (what shift I am working that day) in C7:AG7. Row 5 and 6 are formatted as dates do the days of the week change automatically when I change the year. I keep getting an error message when I use countif or countifs saying that it can't count dates and numbers. Any help you be greatly appreciated.

Mac Studio (2022)

Posted on Aug 30, 2023 10:41 AM

Reply

Answer 1

Top-ranking reply

Recycleur

Level 4

1,657 points

Aug 30, 2023 12:53 PM in response to emt6388

Try this

=COUNTIFS(B7:AH7,">0",B6:AH6,"Sat")+COUNTIFS(B7:AH7,">0",B6:AH6,"Sun")

where I defined the day names as a string that can be compared to a string. IFERROR is to consider shorter months. Copy to the 31 st.

=IFERROR(LEFT(DAYNAME(C5),3),"")

I don't know how you defined the days of the month. For the 1st I'd go with that, which will repopulate the days automatically.

=DATE(A2,MONTH(A5),1)

and from the second to the 31st to take shorter months into account:

=IFERROR(IF(C5+1≤EOMONTH($A5,0),C5+1,""),"")

Reply

Answer 2

Recycleur

Level 4

1,657 points

Aug 30, 2023 5:11 PM in response to emt6388

I made the formulas without the benefit of your hidden rows, so basically you don't need them. You overcomplicate things.

Drop-down or not for the year, the formulas don't care, a numerical value is returned either way.

Yes I made the days of week as text because COUNTIFS can only compare to ready-made values in the range. But it's based on the real date just above, so it's always right for the date: =LEFT(DAYNAME(C5),3). And yes the date above is formatted to just show the day, just like you did.

The COUNTIFS range arguments must be ranges, just ranges not values. Some functions provide a range as their output, like OFFSET for example. This would work. But you cannot put there for example "the day of week of each item of the cells in the range, like DAYNAME(C6:AH6). Well, yes you can, but the result is only the DAYNAME of the value in C6, not a range that COUNTIFS expects, so this may throw an error.

That's why I think you get 0 as a result of your premium formula. What do you compare to what?

For copying to other months you might want to change my formula in C5 to get a fixed year reference ($A$2 instead of A2)

=DATE($A$2,MONTH(A5),1)

I wouldn't use a construction like COLUMNS($C$5:D5,1) just to add 1 to the previous value.

Reply

Answer 3

emt6388 Author

Level 1

8 points

Aug 30, 2023 4:35 PM in response to Recycleur

First of all, thank you for the amazing response! Unfortunately it's not working for me. I don't get an error. Just a value of 0. The way that I had the formulas for the numbers of the month and the days of the week included hidden rows between each month.

The 1st of the month. =$C$3+COLUMNS(C$5,1)−1

Rest of the month. =$C$3+COLUMNS($C$5:D5,1)−1

Both the numbers and the days of the week were formatted as dates with custom formatting to only show the number of the month or the short form of the week.

I do like the way that you did it better a lot cleaner and easier. The only issue is that the year at the top is a drop-down menu so I can choose the new year when appropriate. When I change it to 2024 all the dates from 2 to the end of the month disappear.

As for the formula that counts the weekends with a 1 or a 2. Is it possible that the formula that you listed above is looking for the "Sat" and "Sun" as "text" not a date?

Thank you so much for your help. It's greatly appreciated!

Reply

Answer 4

emt6388 Author

Level 1

8 points

Aug 30, 2023 7:43 PM in response to Recycleur

Well, I figured it out. I accidentally didn't insert the =LEFT(DAYNAME(C5),3) formula to the days of the week. As far as the days of the month disappearing I changed the year. I used the formula =DATE($B$2,1,1) so the year is included. It all works great. Thank you so much for your help!

Reply

Answer 5

Recycleur

Level 4

1,657 points

Sep 2, 2023 8:54 AM in response to emt6388

There's one more thing that kept nagging me this week. Nothing to make or break the spreadsheet, more in the line of Numbers's aesthetics and user-interface for your future projects.

Instead of using the first row of the table with no other purpose than identifying your data, Numbers offers you the possibility to name tables with meaningful names. It is not just for show, it also serves an important purpose when debugging large projects with numerous sheets and tables.

As you can see below, by naming the sheet "Schedule" instead of "Table 1", any reference to a cell in that table originating from another table will immediately ring a bell and help you find table reference mistakes faster in your formulas. Especially if the name is not shown and/or the table is on another sheet. No more "Which one is Table 1, Table 2, Table 3,...?"

Regards.

Reply