# Trying to pull 'Weekly' participation

#### DJFANDANGO

##### Board Regular
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), 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​ B C D E F 1 Name Attended Week 01 Week 02 Week 03 2 Mark 2 1 1 3 John 2 1 1 4 billy 2 1 1 5 oscar 1 1 6 michael 2 1 1 7 Total 10 8 9 Date Name 10 17/01/2019 John 11 15/01/2019 Mark 12 17/01/2019 Billy 13 20/01/2019 Billy 14 21/01/2019 Oscar 15 26/01/2019 John 16 01/01/2019 Michael 17 08/01/2019 Michael 18 15/01/2019 Mark

<tbody>
</tbody>

Sheet 2
 A B C Week 1 2018-12-31 2019-01-06 Week 2 2019-01-07 2019-01-13 Week 3 2019-01-14 2019-01-20

<tbody>
</tbody>

### 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
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
Hi Louis,

You are a STAR!!!!

That worked a treat!!! Thank you!

And for the 'Weekly' extraction? any ideas?

#### LouisT

##### Board Regular

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!

And for the 'Weekly' extraction? any ideas?

#### DJFANDANGO

##### Board Regular
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

In what way doesn't it work?

#### DJFANDANGO

##### Board Regular
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
You missing a couple of &
=COUNTIFS(\$B\$10:\$B\$18,\$A2,\$A\$10:\$A\$18,">="&\$BP\$2,\$A\$10:\$A\$18,"<="&\$BQ\$2)

#### DJFANDANGO

##### Board Regular
Ok, progress, now its returning 0

Replies
10
Views
132
Replies
1
Views
54
Replies
6
Views
94
Replies
2
Views
74
Replies
3
Views
47

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...