Excel - Nested IF statements Help needed

[Hughesy]

Hi all im new here and I need abit of help please.

I am working on a spreadsheet and i need to know how many people where in a place on a particular day. I have two columns full of start and end dates for over 800 people.

I need to work out on a set date (Say the 5th April), how many people where in on that day. So i have created a sheet with every date for a year on and now i need to find out how many where in on each date.

For instance I have:
01/04/07 - 08/04/07
02/04/07 - 02/05/07
01/04/07 - 12/04/07
01/04/07 - 03/04/07

How can i therefore find how many where in on the 05/04/07. Obviously looking at the data there was 3 in on that date, however the problem iv been having is because none of them start or end on that date im struggling to work out how to create a formula that counts them?

Now obviously some people where in a place prior to the 5th April, and say the start date was the 3rd April - 8th April, i would need to count these.

Column AG is the start date
Column AH is the end date

So far i have come up with this, but the last bit isnt working - is there something im doing wrong??

=COUNTIF('Res Wks CF Data'!$AG:$AG,"<08/04/2007")*AND(COUNTIF('Res Wks CF Data'!$AH:$AH,">=07/04/2007"))

Please help asap.

Thanks

 

[ExpandOnline]

try
=SUM(IF('Res Wks CF Data'!$AG:$AG<=date,1,0))-SUM(IF('Res Wks CF Data'!$AH:$AH<date,1,0))

where date is the cell with the attendance date you are querying

IMPORTANT, don't just press enter as it wont work properly, press ctrl-shift-enter to create an array equation - it will look like you have enclosed the equation in curly braces in the toolbar

Hope this helps

 

[Hughesy]

Hi thanks for the reply.

Ideally i want to be able to do a count of each on a seperate worksheet i have set up. My sheet is set up with all the dates for 01/04/07 - 31/03/08
i.e.
01/04/07 =
02/04/07 =
03/04/07 =
04/04/07 =

What i wanted to do was then to simply put a formulae in that would give me a count of each that where in on that date.

Is this possible?

Thanks

 

[ExpandOnline]

Yes, put in the formula I gave, edit it so that date points to the cell with the date in.
Copy downwards.

Peter

 

[Hughesy]

The only problem is that im checking over 800 records at once, to get a count of how many are within that date.

Does your example work for this?

So for example if i have
01/04/07 - 08/04/07
02/04/07 - 02/05/07
01/04/07 - 12/04/07
01/04/07 - 03/04/07

will it count 02/04/07 - 4 times?

 

[ExpandOnline]

I believe so, if you are stuck - email your file to me: [email protected]

 

[Cleveregg]

Did this help http://www.cleveregg.co.uk/forum/showthread.php?t=414 ?