Index query

[scoobyblue]

I have the following formula that is returning VALUE!

{=INDEX($E$11:$F$11104,$E$6,MATCH(1,($D$11:$D$11104>=$D$6)*($D$11:$D$11104<=$D$8),1))}

Columns E & F are populated with surnames, E6 is the surname that is being identified (counted), column D are the Date values, i.e. Fri-17-Feb-12, D6 is the start date (occurrences of surname on or after this date), and D8 is end date. Values in E6, D6 & D8 are those that can be manually amended.

The idea being to count the occurrences of a given surname between the selected dates. Thanks for your help.

 

[Aladin Akyurek]

I have the following formula that is returning VALUE!

{=INDEX($E$11:$F$11104,$E$6,MATCH(1,($D$11:$D$11104>=$D$6)*($D$11:$D$11104<=$D$8),1))}

Columns E & F are populated with surnames, E6 is the surname that is being identified (counted), column D are the Date values, i.e. Fri-17-Feb-12, D6 is the start date (occurrences of surname on or after this date), and D8 is end date. Values in E6, D6 & D8 are those that can be manually amended.

The idea being to count the occurrences of a given surname between the selected dates. Thanks for your help.

Control+shift+enter, not just enter:

=SUM(IF($D$11:$D$11104>=$D$6,IF($D$11:$D$11104<=$D$8,IF($E$11:$F$11104=$E$6,1))))

 

[Snakehips]

scoobyblue,

Try this....

=SUMPRODUCT(--(E11:F11104=E6),--($D$11:$E$11104>=$D$6),--($D$11:$E$11104<=$D$8))

 

[scoobyblue]

Works great, many thanks.

 

[Aladin Akyurek]

Works great, many thanks.

You are welcome. Thanks for providing feedback.