formulas

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

# Formulas

Resolved · Medium Priority · Version 365

Tara has attended:
Excel Intermediate course

### Formulas

I have two tables, one with names across a month and the other with totals for a person's name. I want the second table to automatically add up based on the names from the other table. I can't highlight the total month in a range as i'm trying to only use data from the weekends.

I used the CountIf formula, but it says that there are too many arguments (there are 5 weekends over the month). Should i be using a different formula?

Thanks

Tara

### RE: Formulas

Hi Tara,

I am struggling to picture what you have.

You say "Names across a month". Do you mean you have months in the row and names underneath or ??

The countif formula counts items by a criteria. So if you wanted to say count how many times a specific name appears in a range, you could use countif (or better yet countifs) to do that.

If you give a clear description, we can try to help.

THanks
Scott

### RE: Formulas

Hi Scott,

I have one table that has a column with the day and date from Wednesday 1st March down to Sunday 2nd April (each day in a separate row). Different names will go into the adjacent column (basically who is on call that particular day).

I have another table on the same tab, which then has each person's name (6 people) in one column (different rows) and two separate columns, one for days working on a weekend and the other for days working on week days.

As an example - in the first table, if i put the name Charlie onto Friday 3rd, Saturday 11th and Friday 31st - i then want "3" to populate via a formula into the second table alongside Charlie's name for the column that is a weekend.

I thought the formula would be:
=Countif(B3:B5,B10:12,B17:B19,B24:B26,B31:B33,"Charlie")
If i do Countifs it does something odd and puts 0's in cells below.

THis is causing me issues as i'm splitting the range into weekends and weekdays.

Any help appreciated.

Thanks

Tara

### RE: Formulas

Hi Tara,

To simplify the explanation, let's refer to the first table as the parent table and the second table as the child table.

So what I would do is in the parent table, you add in a column (let's call it DayNumber) that indicates if the date is a weekday or weekend.

You could use a formula like =weekday(datefield, 2). What this will do is output a number 1 - 7 where 1 is Monday and 7 is Sunday.

You can then use an if statement along the lines of
DayType=if(DayNumber >=5, "Weekend", "Weekday").

This will categorise every day as either a Weekend (Fri, Sat, Sun) or Weekday (the other days).

Then in your child table, you have
Name Weekend Weekday (In A1 to C1)

You populate the name column with the 6 people (consider using the formula =UNIQUE(REFER TO THE NAME COLUMN IN THE PARENT TABLE))

Then in B2, you would write:
=countifs(ABSOLUTE REFERENCE TO THE NAME COLUMN IN PARENT TABLE, \$A2, ABSOLUTE REFERENCE TO THE DayType COLUMN IN THE PARENT TABLE, B\$1)

Drag that formula down and across and you should be there.

NOTE: The column headers in cells B1 and C1 are identical to the values in the DayType column so that the countif works where DayType = Weekday or Weekend respectively.

### RE: Formulas

Hi Tara,

I uploaded an example. I put on the Child tab one version done using formulas and another version done using a Pivot Table (along with a timeline so you can slice and dice by date).

Hope that helps.

Thanks
Scott

### RE: Formulas

Hi Scott,

Thanks for teh info - to be honest, i'm completely lost with your answer!

Is there anyway that i can upload a spreasheet to show you what i have? or is the upload function just for trainers?

There are actually more columns than my example, i just tried to simplify it. I have 3 columns alongside the date the in the parent ctable for first on call, second on call and third on call. The child table also represents this. If i could show you my example it would be easier.

Thanks

Tara

### RE: Formulas

Hi Tara,

Info@stl-training.co.uk

Reference that your question is posted on the forum.
https://www.stl-training.co.uk/post-41562-formulas.html #41562

Thanks
Scott

### RE: Formulas

Brilliant - thanks Scott - i will do.

### RE: Formulas

See attached...

I have changed your date field to an ACTUAL date with custom formatting (but it does look slightly different to what you had before) so we can use column C to determine if this is a weekday or weekend.

Hope that helps.

### RE: Formulas

Thanks so much Scott, i really appreciate your help.

I have a much better understanding now of what i need to do in future.

Have a great weekend!

Tara

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Change the Default Width of All Columns in Excel 2010

If you want to change the width of the columns in your Excel 2010 spreadsheet, making them either larger or smaller, here's how:

In the Cells group on the Home tab, click Format.

Hover over the section called Cell Size and a drop down list will appear, select Default Width from this list.

In the Standard Width dialog box, enter the size you want to set as the default width and click OK.

View all Excel hints and tips