Using conditional on ledger data

[skwasha]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi,

I'm using the standard Account Ledger template and it's fine for making the data entry easy. But I wanted to add a sheet that keeps track of monthly totals. So, I put in the following for my january total: =SUM(IF(MONTH(Ledger!A:A)=1, Ledger!D, 0))
Thinking this should total every Credit (D) in the Ledger sheet where the Date (A) is January. Instead, it's just giving me the entire total for D.

Any help?

thanx!

 

[John McGhie]

You need an Array Formula.

{Sigh} I cannot believe they left this out of the help...

http://office.microsoft.com/en-us/excel/HA010872901033.aspx

Cheers

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
Hi,

I'm using the standard Account Ledger template and it's fine for making the
data entry easy. But I wanted to add a sheet that keeps track of monthly
totals. So, I put in the following for my january total:
=SUM(IF(MONTH(Ledger!A:A)=1, Ledger!D, 0))
Thinking this should total every Credit (D) in the Ledger sheet where the Date
(A) is January. Instead, it's just giving me the entire total for D.

Any help?

thanx!

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[skwasha]

Tried that as well... neither of the following worked:
{=SUMIF(Ledger!A:A,"1", Ledger!D)} this returns 0 instead of a sum - I also tried = 1, January, etc. for the condition.
{=SUMPRODUCT((MONTH(Ledger!A:A)=3)*(Ledger!D))} this complains that one of the data types is wrong. Since one column is dates and the other just numbers I can't see how this is a problem? I verified that if I do date calculations on just a single cell it works fine. So, the date data appears fine...

Any other ideas?

Thanx!

 

[John McGhie]

No. You'll need to pull that formula to pieces and see what is happening
cell-by-cell.

Why are you quoting the criteria? That would cause a match on either number
or text character "1". But if the column A contains a date, it won't be
"1", that's a "Month Number" so it needs to be converted.

Change your Column A format to "General" and look to see what is actually in
each cell. If you have a column of months of this year, you would expect to
see something like:


38717
38748
38776
38807
38837
38868
38898
38929
38960
38990
39021
39051

Hope this helps

Tried that as well... neither of the following worked:
{=SUMIF(Ledger!A:A,"1", Ledger!D)} this returns 0 instead of a sum - I also
tried = 1, January, etc. for the condition.
{=SUMPRODUCT((MONTH(Ledger!A:A)=3)*(Ledger!D))} this complains that one of
the data types is wrong. Since one column is dates and the other just numbers
I can't see how this is a problem? I verified that if I do date calculations
on just a single cell it works fine. So, the date data appears fine...

Any other ideas?

Thanx!

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[skwasha]

The following ended up working:
=SUMIF(Ledger!$A:$A,"