Countif/Sumproduct checking two seperate date ranges and one other

[AdmiralGrumble]

Hi everyone, made this account just to ask this question as im racking my brains over it.

I've tried Countif and SumProduct but nothing has worked.

Column A is Date range 1 - When the file was first made
Column B is Date range 2 - When the file was completed.
and Column E is the Handler, the person who closed the file.

What i want to work out is how many files the person closed in one month that was opened in another.

so far i have this...

=SUMPRODUCT((A!B:B<=DATE(2014,3,31))*(A!B:B>=DATE(2014,3,1))*(A!E:E=B12)*(A!A:A<=DATE(2014,5,31))*(A!A:A>=DATE(2014,4,1)))

but it doesn't work, im not sure what the hell im doing wrong as ive gotten it working with a single month, yet duplicating the month requirement and changing the criteria hasn't worked at all.

its probably something really simple and stupid but i though id best ask here coz you all seem like nice and helpful people!

[jewelsharma]

With this formula, do you get an error, or you get 0 as answer?

[AdmiralGrumble]

I just get a 0, not sure what im doing wrong as i know the data is there. it should be coming back with 10

[jewelsharma]

Not to dissuade you from using SUMPRODUCT, but have you tried this with CountIfS already?

Your formula looks fine. Have you double checked the format of dates in the column A & B of your 'A' sheet.

[AdmiralGrumble]

I've tried but nothing has worked thus far. im not sure if the date range im using for it applies to Countifs.

[jewelsharma]

If you share the workbook (just a few sample rows), i'll look at it tomorrow.

[AdmiralGrumble]

Whats the best way to share here? Googledocs?

[XOR LX]

No. You can attach directly. Click on Go Advanced and scroll down to Manage Attachments.

Regards

[AdmiralGrumble]

Here you go!

[jewelsharma]

AdmiralGrumble,
Just a quick question - what does B12 in your SumProduct formula refer to? If you replace that reference with the value, does the formula work?
I'm away from my pc, but i will try it in sometime.

Regards,
Jewel

[AdmiralGrumble]

That refers to the Team, tried that and got the same results unfortunately.

[jewelsharma]

Have we got the "Open Dates" & "Closed Dates" swapped by any chance? Please confirm!

[AdmiralGrumble]

Yes and No.

It was, but i swapped them over. same result :-/

[jewelsharma]

this works on my PC:

Please Login or Register  to view this content.

I just tried it on the same sheet, so I deleted the sheet reference. Does it work for you?

[AdmiralGrumble]

that is PERFECT!!

Thank you so much for the help! This is going to help me get things done so much easier now. Rep for Everyone for being so helpfull!

[AdmiralGrumble]

Resurrecting the old thread to save clogging the place with new ones!

Is there any way to use a Cell Reference for the date as seen here:

Please Login or Register  to view this content.

Ive tried replacing the date before and it doesnt work, like so: (B12 being a random cell for examples sake)

Please Login or Register  to view this content.

[XOR LX]

Hi,

You don't need DATE(). Simply B12 will do, providing the entry in that cell is a proper date.

Regards

[AdmiralGrumble]

Works Brilliant! Thanks!

[XOR LX]

You're welcome!