Hi,
Alright my problem could be difficult to explain.
I have a excel 2007 data with 2 worksheet named "FINISHED" and "TOTAL".
The "FINISHED" worksheet pretty much looks like this.
You can see that the following Book ID has multiple entries here .
"01-011-2002-0", "01-011-0021-0" and "01-011-0111-0"
DATE Book ID No.
2010/8/22 01-011-2002-0 3
2010/8/22 01-011-2002-0 2
2010/8/22 01-011-0021-0 2
2010/8/22 01-011-0031-0 3
2010/8/23 01-011-0101-0 4
2010/8/23 01-011-0111-0 5
2010/8/23 01-011-0021-0 6
2010/8/25 01-011-0021-0 -7
2010/8/25 01-011-0111-0 -6
The "TOTAL" worksheet pretty much looks like this.
The No. came from using IF(A2="","",SUMIF(FINISHED!$B:$B,A2,FINISHED!$C:$C))
This searches the exact Book ID in "FINISHED" worksheet and sums up the number for me.
Book ID No.
01-011-2002-0 5 (Summed 3+2)
01-011-0021-0 1 (Summed 2+6+(-7))
01-011-0031-0 3
01-011-0101-0 4
01-011-0111-0 -1 (Summed 5+(-6))
But I would like to be able to seperate them in DATES too.
So If I want to look at the Total No.only for August 23rd, The chart should look like this.
Book ID No.
01-011-2002-0 0 (Does not count because the ID is only in 8/22)
01-011-0021-0 6
01-011-0031-0 0 (Does not count because the ID is only in 8/22)
01-011-0101-0 4
01-011-0111-0 5
I am hoping to do this without changing anything in the "FINISHED" worksheet.
That's why I am looking a function which I can use in "TOTAL" worksheet.
Something like this:
=IF(FINISHED!$A:$A="2010/8/23", IF(A2="","",SUMIF(FINISHED!$B:$B,A2,FINISHED!$C:$C),
"")
If the row "A" contains the date "2010/8/23", it will SUM the No. as usual, but if the date is different it should do nothing. (I know the above function doesn't make sense at all.)
Sorry for this complicated question and I hope it was understandable.
Does some body know a solution to this?
Thank you.
Bookmarks