Numbers : multiple SUMIF commands

Question

Level 1

13 points

Numbers : multiple SUMIF commands

Hello

I’d be so appreciative with some help to create a formula that solves 2 challenges in relation to a roster creation spreadsheet I’m making. The spreadsheet represents a years roster for 8 people, broken into 4 tabs - where each tab represents 3 months of the roster. Each person is represented in the roster by their initials.

CHALLENGE ONE

I'd please like help to create a formula that uses the SUMIF command (or better, if available) that sums 2 different but regularly repeating groups of rows, in a long list of rows. I have attached a screen grab of a short portion of the spreadsheet - and the summary table in which I've calculated the values manually. It is these values I’d like the formula to create automatically.

I’d like the Summary table to find the number of times each person appears in Col 1, broken into 2 groups :

(a) number of times a given person appears for red outlined cells (ie Tues-Thurs inclusive)

(b) number of times a given person appears for green outlined cells (ie Fri-Sun inclusive)

If the empty rows between each week is a problem, it can be removed - however I find it handy to help create a visual delineation between weeks.

CHALLENGE TWO

It would be amazing if the user can be alerted to the existence of a given person in Cols 1-4, for a given date, if that same person’s initials appear in the ‘Unavailable’ column.

Prior to creating the roster, all persons’ unavailable dates would be noted in Column G (“Unavailable”) by entering their initials. It could be there are several unavailable people on the same date - perhaps they could be entered in the same cell, separated by a comma?

For example, person AG is marked as unavailable in cell G5 for Mon 2 Jan 2023. When the roster creator enters AG into cell C5 (ie tries to enter AG into the roster for Mon 2 Jan, a day for which she is marked as unavailable), the roster creator is alerted to this via Column H (“Alert”), as cell H5 turns bright green - or displays a text string such as “ALERT!” if a colour change is not possible.

The ultimate would be if the actual cell within columns B-E would change colour (or similar) upon the roster creator entering a person marked as unavailable - but I understand if that’s too tricky given what I’m trying to do in Challenge One!

So - there we have it! I’ll be so grateful if one of the Numbers wizards can solve this/these challenges for me!

Matthew

Auckland, NZ

MacBook Pro Apple Silicon

Posted on Jan 14, 2023 2:18 PM

Reply

Answer 1

Ralf-F

Level 4

2,741 points

Jan 14, 2023 4:11 PM in response to MattRA

Hi Matthew,

here a potential solution for Challenge One.

Numbers can not check if you mark cells in a specific color or if you add a red / green frame to it.

Therefore I added 2 support columns.

Column B / Day will calculate the weekday (Monday = 1) and column C will contain the group (red / green) based on the weekday.

Formula for B3=WEEKDAY($A3,2)

Formula for C3=IFS(AND(B3≥2,B3≤4),"Red",AND(B3≥5,B3≤7),"Green",B3=1,"-")

For the Summary I use COUNTIFS, that will allow multiple criteria.

Formula for B2=COUNTIFS(Table 1::$C,$B2,Table 1::$D,C$1)

It will only count rows if group and initial match!

Please check cell D4, not sure why you have 5 in your summary.

The $ signs will preserve the row / column, but you would have to check if the rows / columns are used when you copy the formula.

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Regards Ralf

Reply

Answer 2

Ralf-F

Level 4

2,741 points

Jan 14, 2023 4:47 PM in response to MattRA

Hi Matthew,

here a potential solution for Challenge Two.

You would also need support columns to check who is available.

Formula H3=IFERROR(IF(FIND(D3,$G3)≥1,D3),"X") $ sign in $G3 is needed to preserve the column!

FIND will see if the initials from D3 can be found in G3, if Yes the the initials from D3 will be entered in H3.

If it is not found an X will be entered in H3, that is done with IF and IFERROR function.

Conditional Highlighting for D3 would be based on H3, as soon as they have the identical text it will be red.

You have to click here and then select H3.

Now you have to transfer the Conditional Highlighting to the rest of your cells.

Please do a few spot checks to verify that it will shift correctly.

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Regards Ralf

Reply

Answer 3

Ralf-F

Level 4

2,741 points

Jan 17, 2023 11:33 PM in response to MattRA

Hi Matthew,

glad that I could help, looks like we are getting to a solution.

You are correct, I need the Day and Group as a support.

COUNTIFS need something the separate the different groups, you can call them Group A / B or Red / Green.

As lang as you select the column for the COUNTIFS it will use all entries that are in the table, therefore your 3 month will be used.

Here a version with Group A / B

You can place these support columns where ever you like, I add them normally close to the data that I use.

As soon as everything works as it should you can hide them, then nobody will get confused.

Just select the columns that you want to hide and do a right click on your mouse.

Based on the Letters for the columns you can see that some are hidden.

If needed you can always unhide them, just click in the column header and then right click and you get this menu.

Regards Ralf

Reply

Answer 4

Ralf-F

Level 4

2,741 points

Feb 14, 2023 1:50 PM in response to MattRA

Hi Matthew,

looks liken you are getting closer to your solution.

Here the formula for your summary.

Formula for C2=COUNTIFS(Table 1::$C,$B2,Table 1::$D,C$1)

COUNTIFS will count all rows as long as all listed criteria are met.

You have two criteria that must be met

A) Table 1::$C,$B2 it will check in Table 1 column C if the group from cell B2 is found

B) Table 1::$D,C$1 it will check in Table 1 column D if the name from cell C1 is found

Only if both are found in the same row it will count.

Formula from C2 can be populated for the complete row

Formula for C3 would need the column E in table 1

You wrote "I entered the day numbers (column B) manually."

Here the formula to get the day

Hope that these information will help.

Regards Ralf

Reply

Answer 5

Feb 28, 2023 7:01 AM in response to MattRA

I suggest changing the original data table.

I'll rebuild it and make suggestions as we go:

Enter the dates in column A as show (use whatever format you want)

Add a new column (column B) where we will break out the day name

select cell B2, then type, or copy and paste from here, the formula:

=DAYNAME($A3)

shorthand for this is:

B2=DAYNAME($A3)

select cell B2, copy

select cell B2 thru the end of column B, paste

Add a new column C

C2=IF(AND(WEEKDAY($A3,3)≥1, WEEKDAY($A3,3)≤3), 1, IF(AND(WEEKDAY($A3,3)≥4, WEEKDAY($A3,3)≤6), 2,0 ))

select cell C2, copy

select cells C2 thru the end of column C, paste

Now let's make the summary table:

Add a couple new header columns as shown to allow a single formula

D3=COUNTIFS(1STQ_2023::$C, $C3, OFFSET(1STQ_2023::$C$3, 0, $B3,ROWS(1STQ_2023::$A)−2, 1),D$2)

select cell D3, copy

select cells D3 thru F6, paste

Reply

Answer 6

MattRA Author

Level 1

13 points

Jan 17, 2023 10:16 PM in response to Ralf-F

Hi Ralph

Finally found time amongst this busy holiday to check on this.

For Challenge 1:

I think I may have mislead you (and over-complicated things - sorry!) : I only added the green and red borders to show which groupings (of days) I needed to sum. Let's call the 2 groups as follows :

•Group A (Tues+Wed+Thurs)

• Group B (Fri+Sat+Sun)

I need to sum the number of occurrences of a given person (eg AG) in every Group A (for the entire 3 month period) - hence me highlighting these in red, in my example. I don't actually need them highlighted in the spreadsheet. The other group I need to sum is Group B, and the number of occurrences of a given person (eg AG), ) in that group (for the entire 3 month period) - hence me highlighting these in green, in my example. As you can imagine (but not see from my example, as it only shows the first 3 and a bit weeks of the 3 month period), there are lots occurrences of each group : 13 by my calculation. I think I understand why you gave each of the days a number (ie Monday = 1 etc) : so you can tell the formula to only sum incidences of AG in days 2,3,4 (Tues, Wed, Thurs) - is that correct? It's fine to have that data in the spreadsheet - I just need it off to the side, so that when I present the roster, it doesn't contain superfluous info that will confuse the people in the roster.

For Challenge Two

Looks like we're on track for this - I'll test the formula within the coming hours.

Notes:

I have removed the empty rows between each week for purposes of clarity - and perhaps formula creation!
I have 4 columns (Col 1 -4) as there are 4 different subsets of data I need to sum - but they will all use a version of the same formula.
In my region, we write one thousand as 1,000.00.

Does that help - or make it worse?!

Matthew

Reply

Answer 7

MattRA Author

Level 1

13 points

Jan 21, 2023 9:11 AM in response to MattRA

Hi Ralf - I had no idea one could hide columns in Numbers - how cool!

I haven't had time to delve into this since arriving back from a holiday in Japan, and today we're off camping for 5 days and will be out of range - so I'll need to put this on ice until I'm back. I'm sorry about this stop-start nature - but am super keen to get this sorted once I'm back. I'm going to take screen grabs of our conversation so far, so even I can work through implementing it while away camping - even if I can't respond on this forum due to being out of range.

Matthew

Reply

Answer 8

MattRA Author

Level 1

13 points

Feb 14, 2023 6:25 AM in response to MattRA

Hi Ralf - I'm back - if only for a few days! Am off again on Friday on an offshore boat voyage, so won't have signal for about 10 days - but have woken at 3am to do some work on this. I've configured my sheet to look like yours - the most work done in Column C, where I entered that very long formula - which produces the Group A / group B thing. I filled that right to the bottom of this 3 month period. I entered the day numbers (column B) manually. I've added some extra header info in rows 1 & 2 of columns B,C,D,E - as that's what the actual columns refer to. I haven't got the highlighting colour working (for people who are rostered on when they're marked as unavailable in Column I, but that should be easy.

What I can't figure out (it is 3am!) is how the Summary table formula should be - could you please give me an example?

Thanks a million again - we're close!

Matthew

Reply

Answer 9

MattRA Author

Level 1

13 points

Feb 16, 2023 9:47 AM in response to Ralf-F

Hi Ralf - thanks again for all your help. I've entered that formula in the Summary table, and it appears to be doing as requested : awesome!

I have had trouble entering the formula in Table 1:B5 (in your sample it's cell B3) : I can't seem to get it to work - I think it has something to do with the "Monday is 1" part.

Also, I've created the conditional formatting in Table 1 row 5 (for columns D, E, F, G) and it works - how do I get that conditional formatting to 'fill down' in each column?

Regards

Matthew

Reply

Answer 10

Ralf-F

Level 4

2,741 points

Feb 16, 2023 2:21 PM in response to MattRA

Hi Matthew,

when you start the WEEKDAY function it should look like this.

Click on the symbol "date" and select the cell with the date, in this case A3

Then it should look like this

Now select the triangle behind "first-day" and this menu should open.

Here you select "Monday is 1"

As a result you should get this.

To copy the conditional highlighting to the other cells you do the following steps.

Select the cells in columns D, E, F, G in row 5
Push "option + command + C"
Select the cells in columns D, E, F, G in row 6 to xx
Push "option + command + V"

That should copy the conditional highlighting in the rest of your cells.

Enjoy your trip.

Regards Ralf

Reply

Answer 11

MattRA Author

Level 1

13 points

Feb 16, 2023 6:45 PM in response to Ralf-F

Hi Ralf

Have managed to correct the Day numbering formula thanks to your very accurate guidance.

However, when I try to copy the conditional formatting in D,E,F,G of Row 5, using "option + command + C", another app I use (Text Expander) opens up a dialogue box! I've searched through the Preferences for Text Expander (hoping to discover where that keystroke had been assigned) but couldn't find it - I also searched through the OS's Keyboard preference pane to see if I could see where it had been assigned : no luck! I've looked in the Edit menu (and others) in Numbers to see if there was a modified copy command - but I can't see one. Any hints as to what to do?

Matthew

Reply

Answer 12

MattRA Author

Level 1

13 points

Feb 16, 2023 6:56 PM in response to MattRA

Well, I had a hunt around on the net & found a guide to copying conditional formatting - and did that (via the Format menu) - and it appears to work!

Now I just need to create some more conditional formatting rules that allow me to colour each person's initials in a different colour - I'll play around to see if I can sort it all by myself. 😉 I'm hoping it won't clash with the red highlight rule we've setup.

Reply

Answer 13

MattRA Author

Level 1

13 points

Feb 28, 2023 1:50 AM in response to MattRA

Hi Ralf

Well, it seems to work!!! There are still a few tweaks that I may try to implement myself (introducing a column that one marks with an X to indicate a public holiday, and then this is summarised in the Summary table) - but I think I can do that by copying the formula you've created -and modifying them a little.

Thank you so much for creating this spreadsheet for me - it's very much appreciated. It'll create the rosters for NZ's only children's hospital, so you can feel proud that you've created a roster system that enables doctors look after sick children.

Matthew

PS - one other issue I'm having in Numbers that I haven't experienced before : in one particular spreadsheet I have, when I try to copy data from a column, and past it into a table in a Pages document) it refuses to paste! I hit 'command c' to make the copy - and the Edit menu flashes - but when I try to paste it into the table in the Pages document, the Edit menu flashes - but nothing comes out! When I try to copy from the same source and paste it into a different (new) blank Numbers spreadsheet, it pastes as expected - but when I try to paste it into a table embedded in a new Pages file, it also fails. Any ideas why?

Reply