Conditional Sum

[harman]

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 ...

[Mark Hone]

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
>
>