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

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