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]

 

[DJFANDANGO]

Although I manually changed the date for it to show 'week 1', its now showing 0 (when it should show 1)

 

[Fluff]

It works for me

Excel 2013/2016
	A	B	C	D	E	F	G	BN	BO	BP	BQ
1	Attended			Week 01	Week 02	Week 03
2	Mark	2		1	0	1			Week 1	31/12/2018	06/01/2019
3	John	2		0	0	1			Week 2	07/01/2019	13/01/2019
4	Billy	2		1	0	1			Week 3	14/01/2019	20/01/2019
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	05/01/2019	Mark
12	17/01/2019	Billy
13	01/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,">="&$BP$2,$A$10:$A$18,"<="&$BQ$2)
E2	=COUNTIFS($B$10:$B$18,$A2,$A$10:$A$18,">="&$BP$3,$A$10:$A$18,"<="&$BQ$3)
F2	=COUNTIFS($B$10:$B$18,$A2,$A$10:$A$18,">="&$BP$4,$A$10:$A$18,"<="&$BQ$4)

 

[DJFANDANGO]

Hey Fluff,

OK... Major progress, its working, but... it doesnt like coming from 2018 - 2019???

I changed the december 18 date to 01-01-2019 and it WORKED!! I dont understand why it doesnt like the 2018 date?

I've tried formatting all the date cells to DATE without any success...

What do you think?

 

[Fluff]

It should work with 2018 dates.
Easiest way to check your dates is to format the dates as General & they should become numbers like 43465 (which is 31/12/2018)

 

[DJFANDANGO]

Hi,

Yeah, I tried that and the numbers appear like you say.

It's so frustrating as I got it to 'work' but it was finding data that WASNT there...??? i.e. I put in only dates till 28th Feb, but when I dragged the formula accross (removing the $ lock of course) it was still populating random numbers in each weekly block...

I'll try upload the 'actual' file in google drive or something so you can have a look?

Thanks

 

[DJFANDANGO]

Hi Fluff,

You would NOT believe it... it was 'how' I was pasting the information into the spreadsheet, I have to 'Keep Source formatting' proper DUH! moment for me, once again, thanks very much for helping and beiong so patient with my on and on questioning, it is working an absolute TREAT! exactly what I need for my KPI's!!!

Top Advice, thanks!

 

[Fluff]

Glad you figured it out & thanks for the feedback