count blank cells based on row and column range criteria

[junglerose]

A	B	C	D	E
	3/31/2014	4/7/2014	4/14/2014	5/12/2014
Amy	x
Bill	x	x
Tom			x
	4/2/2014	4/9/2014	4/16/2014	5/16/2014
Amy		x

<tbody>
</tbody>

I'm trying to count missed attendance based on the person's name (column a) and a range of dates (rows 1 and 6) before today. So I want to count the blank cells in all the rows that say "Amy" that fall on a date before today (so even though E2 is blank, I don't want to count it). There are different groups of people who have to meet on different days so I wanted to have one master worksheet that calculated how many meetings each person has missed. One person may have to go to multiple meetings during the week which is why their name comes up twice. Is there a formula that can search for a name, figure out if cells in the row are blank AND before today and then count them. Or do I have to make a different worksheet for every group of dates? Thanks for any help or advice anyone can offer.

 

[pbornemeier]

This would work for Amy
=SUMPRODUCT(--($B$2:$E$2<=NOW()),--(B3:E3=""))+SUMPRODUCT(--($B$6:$E$6<=NOW()),--(B7:E7=""))

Do you only have 3 people?
Do you only have 4 date columns?
Are there only 2 sets of names?

 

[junglerose]

This would work for Amy
=SUMPRODUCT(--($B$2:$E$2<=NOW()),--(B3:E3=""))+SUMPRODUCT(--($B$6:$E$6<=NOW()),--(B7:E7=""))

Do you only have 3 people?
Do you only have 4 date columns?
Are there only 2 sets of names?

Thank you so much! I was using TODAY instead of NOW and it just wasn't working! I have 44 people and I kind of wanted it to search for the name... but I can manually select each instance when they attend. Some people are mentioned three times and others just once or twice. The dates are ongoing. I was thinking about making a new worksheet for every year. Thank you for solving my problem.

 

[pbornemeier]

You are welcome. TODAY() should also work.

Why are there 2 rows of names? If there was only 1 row then the formula could be modified to tally a list of absences next to the name.

 

[junglerose]

You are welcome. TODAY() should also work.

Why are there 2 rows of names? If there was only 1 row then the formula could be modified to tally a list of absences next to the name.

A different group of people are required to attend on different days. So for example, Amy has to attend on Mondays and Wednesdays, Bill has to come on Thursdays and the every other Tuesday. So I have each row of dates for those specific groups of people. Then what I was looking to do was have a separate space that tracked how many times Amy missed attendance (for example). Whether she missed on Monday or missed on Wednesday. The reason I have it in groups of when they have to attend (instead of one long list of calendar dates) is so I know when they were supposed to be there- since one person doesn't need to attend all dates.

 

[junglerose]

A different group of people are required to attend on different days. So for example, Amy has to attend on Mondays and Wednesdays, Bill has to come on Thursdays and the every other Tuesday. So I have each row of dates for those specific groups of people. Then what I was looking to do was have a separate space that tracked how many times Amy missed attendance (for example). Whether she missed on Monday or missed on Wednesday. The reason I have it in groups of when they have to attend (instead of one long list of calendar dates) is so I know when they were supposed to be there- since one person doesn't need to attend all dates.

Is there any way to search for a name and use the previous formula? It's a lot of work to manually punch in all the cell numbers because then if I go back and add a person to a group, or change a person, I have to change all the cell numbers in the formulas too.

 

[pbornemeier]

Do your dates always occupy columns B:E?
How many different groups are there?
What would the ideal output look like?
It would be possible to do if each group was on as separate worksheet, I am not sure if it can be done with formulas with multiple date rows that might change locations.

 

[junglerose]

Do your dates always occupy columns B:E?
How many different groups are there?
What would the ideal output look like?
It would be possible to do if each group was on as separate worksheet, I am not sure if it can be done with formulas with multiple date rows that might change locations.

I could put enough space in between each section (each day of the week) so that I'd have room to move names and add names and so the dates wouldn't change- so they would always occupy columns B:E. There are eight groups- one for each day of the week and then three more that are a different kind of meeting (but the same people). Ideally, I would have three worksheets (one for meeting type 1, one for meeting type 2, and one for all the employee names and their total absences next to them.)

 

[pbornemeier]

What would be your ideal workbook/worksheet layout? Please be specific. Would a Visual Basic solution be OK (code instead of formulas) ? There are no dates for the last part of April/first part of May in the example in the original post. I assume that was a period when there were no meetings scheduled.

 

[junglerose]

What would be your ideal workbook/worksheet layout? Please be specific. Would a Visual Basic solution be OK (code instead of formulas) ? There are no dates for the last part of April/first part of May in the example in the original post. I assume that was a period when there were no meetings scheduled.

There's a meeting every week, except Tuesdays which are every 2nd and 4th Tuesday of the month. I would like the dates to continue until the end of the worksheet (I started the dates in April and just copied four lines over to paste in here). Then on another worksheet will be a list of all the employee names and a sum total of how many meetings they have missed up until today. Ideally, this list will be able to search for the name in all the mini-tables (each group meeting) and count the absences. I would also be able to add a name to the master list and plug them into a group without having to change the rest of the search formulas. For example, if I searched for Amy, it would show 4 absences (counting 5/13). If I look at Bob's name in the master list, I would see that he has 3 absences. I'm not sure if Visual Basic would be better. I've never worked with Visual Basic so I don't know if that would be a more practical solution. Any advice you have I'd be happy to hear.

Amy	4
Angela	6
Bob	3
Claire	3
Elizabeth	7
Henry	4
James	6
Jennifer	3
Julia	4
Karissa	7
Kelly	5
Kyle	4
Lacy	4
Leslie	3
Mark	7
Nicole	4
Robert	3
Robin	3
Sandy	2
Scott	3
Spenser	2
Stephanie A	3
Stephanie B	4
Steven	6

<tbody>
</tbody><colgroup><col><col></colgroup>

Monday	3/31/2014	4/7/2014	4/14/2014	4/21/2014
Angela		x
Karissa
Kelly	x	x	x
Lacy	x	x	x	x
Leslie	x	x	x
Mark		x	x	x
Robert	x	x	x	x
Tuesday (2 and 4)	4/8/2014	4/22/2014	5/13/2014	5/27/2014
Amy	x	x
Jennifer
Julia	x	x
Kelly		x
Lacy	x	x
Mark		x
Sandy		x
Wednesday	4/2/2014	4/9/2014	4/16/2014	4/23/2014
Amy	x	x
Karissa		x
Claire	x
Henry
Kyle	x	x
Elizabeth	x
James	x	x
Julia	x	x
Kelly	x	x
Lacy		x
Leslie	x	x
Mark
Nicole
Robin	x	x
Robert		x
Scott	x	x
Spenser	x
Stephanie A	x	x
Stephanie B		x
Steven	x
Bob	x	x
Angela	x
Thursday	4/3/2014	4/10/2014	4/17/2014	4/24/2014
Amy	x		x	x
Julia	x			x
Robin	x		x	x
Stephanie A	x		x	x
Bob	x		x	x
Friday	4/4/2014	4/11/2014	4/18/2014	4/25/2014
Kyle	x			x
Elizabeth
James				x
Scott	x	x		x
Stephanie B	x	x		x
Steven				x

<tbody>
</tbody><colgroup><col><col><col><col span="2"></colgroup>