Correct formula

[maddog9486]

I am trying to take data from sheet INCIDENTS and report that on sheet DATA.

On my sheet named Incidents, I have 2 columns...one for DATE OPENED and one for DATE CLOSED.

On the DATA sheet I'm trying to show all items from DATE OPENED that have a close date (DATE CLOSED column) of 6/01/2008 or later and including the ones that have nothing in the cell (meaning they are still OPEN or active).

I have tried everything under the sun and can not get it to work to save my life. I tried SUMPRODUCT, SUM, SUMIF, but i must be typing something wrong or using the wrong formula completely. Any help would be greatly appreciated.
Thanks.

[daddylonglegs]

Assume Date Opened column is A and Date Closed is B try

=SUMPRODUCT(--ISNUMBER(INCIDENTS!A2:A100),(INCIDENTS!B2:B100>="6/1/2008"+0)+(INCIDENTS!B2:B100=""))

Adjust ranges as necessary

[maddog9486]

Ok, well I tried that formula but it was not working, it's reporting the wrong number.

I attached a file showing what i'm looking for:
test.GIF

column E is the opened date
column F is the closed date

I need every date in column E that is before 06/01/2008 but of the ones that are before that date they have to either still be open (active...i.e blank cell) or have a closed date (column F) of 06/01/2008 or later. I hope that explained it a little better.

[NBVC]

How about?

=SUMPRODUCT(--(INCIDENTS!E2:E100<"6/1/2008"+0),(INCIDENTS!F2:F100>="6/1/2008"+0)+(INCIDENTS!F2:F100=""))

adjust ranges to suit.

[maddog9486]

NBVC, i tried adjusting the ranges but it returns more than the first formula did. Thank you for the effort though. I guess i'll just leave it with no formula. Thanks guys.

[NBVC]

If you attach the actual sheet and tell us your expected result, it would be easier to come up with the correct formula or figure out why the formula is not giving the expected results.

[maddog9486]

Here is the sheet:
COPY of BPO - Metrics Workflow Incident Log.xls

1)If you click the GRAPHS sheet and go to June column where the #8 is...That is what the number is supposted to be currently, i'm wanting that to auto update as I add or subtract items. (The data is on the INCIDENTS sheet).

2)I am needing to do the same thing with the time range on the bottom graphs table from the respective data on the INCIDENTS sheet but if someone could help me with problem #1 that would be great and I can tweak that formula for the time scale information.

Thanks.

[NBVC]

If there are blanks in the E2:E100 range, you will get incorrect results...

so try this formula in C3:

=SUMPRODUCT(--(Incidents!E3:E100<"6/1/2008"+0),--(Incidents!E3:E100>0),(Incidents!F3:F100>="6/1/2008"+0)+(Incidents!F3:F100=""))


I am not sure what your conditions for the bottom table are... if you can't figure out how to tweek the above formula to suit, then post details here so we can get you a formula.