Trying to pull 'Weekly' participation

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
111
Office Version
  1. 365
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>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
Solution
Hi Louis,

You are a STAR!!!!

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

And for the 'Weekly' extraction? any ideas?
 
Upvote 0
How about


Excel 2013/2016
ABCDEF
1NameAttendedWeek 01Week 02Week 03
2Mark2002
3John2001
4billy2002
5oscar1000
6michael2110
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
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
In what way doesn't it work?
 
Upvote 0
Thanks for the quick reply Fluff,

I copied and pasted the exact formula you gave me and it's returning a #Value error...?
 
Upvote 0
You missing a couple of &
=COUNTIFS($B$10:$B$18,$A2,$A$10:$A$18,">="&$BP$2,$A$10:$A$18,"<="&$BQ$2)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top