Trying to pull 'Weekly' participation

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
Good day all,

I'm trying to pull the weekly attendance of personnel, at the moment i do this manually (not clever enough to do the other bit:confused:), please see my screenshot of an example, I have on another sheet all the week numbers and dates they start/finish on.

So I get the information sent to me and I paste it into the cells A10:B18, and this gived me the 'Meetings Attended'

The only formula I'm using is =COUNTIF(B$10:B$18,"Mark"), and the same in the subsequent cells below with each "name" A2:A6


  1. - How do I get it to populate the info on the 'Week 01, 02, 03...etc"?
  2. - Is there a way of changing the =COUNTIF to pick up whats in the cell (A2:A6) instead of writing the name in the formula (B2:B6), as the names list changes from week to week?

Hope this is not too long winded, and you guys can help me out with probably a VERY simple solution...

I'd really appreciate any help on this one, thanks

Sheet 1
A
BCDEF
1NameAttendedWeek 01Week 02Week 03
2Mark211
3John211
4billy211
5oscar11
6michael211
7Total10
8
9DateName
1017/01/2019John
1115/01/2019Mark
1217/01/2019Billy
1320/01/2019Billy
1421/01/2019Oscar
1526/01/2019John
1601/01/2019Michael
1708/01/2019Michael
1815/01/2019Mark

<tbody>
</tbody>

Sheet 2
ABC
Week 12018-12-312019-01-06
Week 22019-01-072019-01-13
Week 32019-01-142019-01-20

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

LouisT

Board Regular
Joined
Apr 5, 2010
Messages
61
DJFandango all you need to do is instead of typing "Mark" in the Countif formula, just select the cell reference for the name. Would look like this: =COUNTIF(B$10:B$18,$A2)
If i understood your question correctly that should fix it.

LouisT
 

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
Hi Louis,

You are a STAR!!!!

That worked a treat!!! Thank you!:biggrin:

And for the 'Weekly' extraction? any ideas?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;;">Name</td><td style="text-align: center;;">Attended</td><td style="text-align: center;;"></td><td style="text-align: center;;">Week 01</td><td style="text-align: center;;">Week 02</td><td style="text-align: center;;">Week 03</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Mark</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">John</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">billy</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">oscar</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">michael</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">Total</td><td style="text-align: center;;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Name</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">17/01/2019</td><td style="text-align: center;;">John</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">15/01/2019</td><td style="text-align: center;;">Mark</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">17/01/2019</td><td style="text-align: center;;">Billy</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">20/01/2019</td><td style="text-align: center;;">Billy</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">21/01/2019</td><td style="text-align: center;;">Oscar</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">26/01/2019</td><td style="text-align: center;;">John</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">01/01/2019</td><td style="text-align: center;;">Michael</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">08/01/2019</td><td style="text-align: center;;">Michael</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">15/01/2019</td><td style="text-align: center;;">Mark</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$10:$B$18,$A2,$A$10:$A$18,">="&Sheet2!$B$1,$A$10:$A$18,"<="&Sheet2!$C$1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$10:$B$18,$A2,$A$10:$A$18,">="&Sheet2!$B$2,$A$10:$A$18,"<="&Sheet2!$C$2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$10:$B$18,$A2,$A$10:$A$18,">="&Sheet2!$B$3,$A$10:$A$18,"<="&Sheet2!$C$3</font>)</td></tr></tbody></table></td></tr></table><br />
 

LouisT

Board Regular
Joined
Apr 5, 2010
Messages
61

ADVERTISEMENT

Glad it worked. Excel has a formula called "WeekNum" that you can use to reference any date. if you use that in an "IF" statement it should do the work for you. =WEEKNUM(B2) would result in "53" so if you use that formula it will pull it into the right cell.

Hi Louis,

You are a STAR!!!!

That worked a treat!!! Thank you!:biggrin:

And for the 'Weekly' extraction? any ideas?
 

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
Hey Fluff,

Thanks for that, I am struggling with the &sheet2! bit, so, I've moved the dates into Sheet1 at the end: start date in BP2 end date BQ2...

I've tried this based on your help, but its not working, could you enlighten me? many thanks..
=COUNTIFS($B$10:$B$18,$A2,$A$10:$A$18,">="$BP$2,$A$10:$A$18,"<="$BQ$2)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In what way doesn't it work?
 

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the quick reply Fluff,

I copied and pasted the exact formula you gave me and it's returning a #Value error...?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
You missing a couple of &
=COUNTIFS($B$10:$B$18,$A2,$A$10:$A$18,">="&$BP$2,$A$10:$A$18,"<="&$BQ$2)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,444
Messages
5,528,797
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top