Trying to pull 'Weekly' participation

[DJFANDANGO]

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
[TABLE="class: grid, width: 625"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD]

A
​

[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Attended[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Week 01[/TD]
[TD="align: center"]Week 02[/TD]
[TD="align: center"]Week 03[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Mark[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]John[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]billy[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]oscar[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]michael[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]Total[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]17/01/2019[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]15/01/2019[/TD]
[TD="align: center"]Mark[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]17/01/2019[/TD]
[TD="align: center"]Billy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]20/01/2019[/TD]
[TD="align: center"]Billy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]21/01/2019[/TD]
[TD="align: center"]Oscar[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]26/01/2019[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: center"]Michael[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: center"]Michael[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"]15/01/2019[/TD]
[TD="align: center"]Mark[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]Week 1[/TD]
[TD]2018-12-31[/TD]
[TD]2019-01-06[/TD]
[/TR]
[TR]
[TD]Week 2[/TD]
[TD]2019-01-07[/TD]
[TD]2019-01-13[/TD]
[/TR]
[TR]
[TD]Week 3[/TD]
[TD]2019-01-14[/TD]
[TD]2019-01-20[/TD]
[/TR]
</tbody>[/TABLE]

 

[LouisT]

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]

Hi Louis,

You are a STAR!!!!

That worked a treat!!! Thank you!

And for the 'Weekly' extraction? any ideas?

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	Name	Attended		Week 01	Week 02	Week 03
2	Mark	2		0	0	2
3	John	2		0	0	1
4	billy	2		0	0	2
5	oscar	1		0	0	0
6	michael	2		1	1	0
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
Sheet1

Cell Formulas
Range	Formula
D2	=COUNTIFS($B$10:$B$18,$A2,$A$10:$A$18,">="&Sheet2!$B$1,$A$10:$A$18,"<="&Sheet2!$C$1)
E2	=COUNTIFS($B$10:$B$18,$A2,$A$10:$A$18,">="&Sheet2!$B$2,$A$10:$A$18,"<="&Sheet2!$C$2)
F2	=COUNTIFS($B$10:$B$18,$A2,$A$10:$A$18,">="&Sheet2!$B$3,$A$10:$A$18,"<="&Sheet2!$C$3)

 

[LouisT]

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]

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]

In what way doesn't it work?

 

[DJFANDANGO]

Thanks for the quick reply Fluff,

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

 

[Fluff]

You missing a couple of &
=COUNTIFS($B$10:$B$18,$A2,$A$10:$A$18,">="&$BP$2,$A$10:$A$18,"<="&$BQ$2)

 

[DJFANDANGO]

Ok, progress, now its returning 0