Finding a column and summing date range for that column

**emesem5** · 09-18-2012, 04:50 PM

I am trying to search for the name of a person, and sum the column below between a date range. I can't seem to get any method to work.

think someone could give me a hand please?

Adam J Andrew L Brad D Karl V Kris L Paul L Tim S Yongen W
9/1/2012 OFF 1 VAC OFF OFF 1 OFF off
9/2/2012 1 OFF VAC OFF 1 OFF OFF OFF
9/3/2012 1 STAT VAC STAT 1 STAT STAT STAT
9/4/2012 1 1 VAC 1 1 1 1
9/5/2012 1 1 VAC 1 1 1 1
9/6/2012 OFF 1 VAC 1 1 LIEU 1
9/7/2012 OFF 1 OFF 1 1 1 1
9/8/2012 OFF OFF OFF OFF OFF OFF 1
9/9/2012 OFF OFF OFF OFF OFF OFF OFF
9/10/2012 1 Lieu 1 1 LIEU 1 OFF
9/11/2012 1 Lieu 1 1 LIEU 1 1
9/12/2012 1 Lieu 1 1 LIEU 1 1
9/13/2012 1 Lieu 1 LiEU LIEU 1 1
9/14/2012 1 Lieu 1 LiEU LIEU LIEU 1
9/15/2012 OFF OFF OFF OFF OFF OFF 1
9/16/2012 OFF OFF OFF OFF OFF OFF OFF
9/17/2012 1 1 1 OFF off OFF off

**TMS** · 09-18-2012, 04:58 PM

The presentation of your problem is not the best. Please upload a sample workbook, ideally with a mock up of your expected results.

Regards, TMS

**emesem5** · 09-18-2012, 05:29 PM

Unfortunately, my actual sheet is 20Mb, but this should give you the idea of what I want to use. the table to the right is on a different sheet in my actual Excel file.

**TMS** · 09-18-2012, 06:01 PM

If you name the ranges with the individual's name, then you can use:

=COUNTIFS(INDIRECT(SUBSTITUTE(N7," ","_")), "<>OFF", INDIRECT(SUBSTITUTE(N7," ","_")), "<>STAT", INDIRECT(SUBSTITUTE(N7," ","_")), "<>VAC", INDIRECT(SUBSTITUTE(N7," ","_")), "<>Lieu", $A$2:$A$18, ">=" & P$3, $A$2:$A$18, "<=" & Q$3)

Note that, if you select the data in each column and use "Create from selection", the space will be replaced by an "_", hence the reason for the SUBSTITUTE.

Regards, TMS