Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Sum
Hi, I am trying to get some information out of a sheet using conditional sum wizard. BookCode BookYear StatementDate Deposit TotalReceivable SL 03 9/1/2005 180.00 1,195.00 SL 03 9/1/2005 704.00 4,689.00 SL 03 9/1/2005 703.35 4,689.00 NH 04 9/1/2005 2,250.00 15,000.00 RR 04B 9/1/2005 10.00 55.00 DP 03 9/1/2005 - 1,343.00 WH 02 9/1/2005 229.17 687.50 I get a reasult back for (BookCode=RR and BookYear=04B) but if I use any other *BookYear *it always returns a 0 for example if i want sum of Deposit where BookCode=SL and BookYear = 03 it returns a 0. I Think the leading 0 in the BookYear is the problem but then why does BookYear=04B work. - Any help is appreciated ... -- harman ------------------------------------------------------------------------ harman's Profile: http://www.excelforum.com/member.php...o&userid=27109 View this thread: http://www.excelforum.com/showthread...hreadid=466248 |
#2
|
|||
|
|||
Hi Harman,
I think the issue is that the conditional sum wizard is trying to convert the criteria you select into a number so 03 becomes 3 and then it can't find a match. The simplest fix appears to be to prefix your book years with a non-numeric digit so Excel cannot convert them to numbers. If you prefix each book year with a 'Y' so e.g. 03 becomes Y03 then the wizard seems to work OK. An alternative would be to convert all Book Years that can be numbers into numbers and then treat them as numbers in the wizard so 03 becomes 3. You could then use a custom format on the column to format the figure as 03. Hope this makes sense and helps, Mark "harman" wrote: Hi, I am trying to get some information out of a sheet using conditional sum wizard. BookCode BookYear StatementDate Deposit TotalReceivable SL 03 9/1/2005 180.00 1,195.00 SL 03 9/1/2005 704.00 4,689.00 SL 03 9/1/2005 703.35 4,689.00 NH 04 9/1/2005 2,250.00 15,000.00 RR 04B 9/1/2005 10.00 55.00 DP 03 9/1/2005 - 1,343.00 WH 02 9/1/2005 229.17 687.50 I get a reasult back for (BookCode=RR and BookYear=04B) but if I use any other *BookYear *it always returns a 0 for example if i want sum of Deposit where BookCode=SL and BookYear = 03 it returns a 0. I Think the leading 0 in the BookYear is the problem but then why does BookYear=04B work. - Any help is appreciated ... -- harman ------------------------------------------------------------------------ harman's Profile: http://www.excelforum.com/member.php...o&userid=27109 View this thread: http://www.excelforum.com/showthread...hreadid=466248 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
How do i create a conditional sum? | Excel Worksheet Functions | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) |