Correct formula
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.
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
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.
Last edited by maddog9486; 07-01-2008 at 09:07 AM.
How about?
=SUMPRODUCT(--(INCIDENTS!E2:E100<"6/1/2008"+0),(INCIDENTS!F2:F100>="6/1/2008"+0)+(INCIDENTS!F2:F100=""))
adjust ranges to suit.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks