Excel Formulas in General Ledger
 

Hi,

I am creating a Ledger that has to evaluate two columns - A
description column and a date column. If the result of both are true,
it populates another worksheet with the amount entered in a 3rd
column. In this case, both arguments must be true to have the figure
included in the summarization.

It works using only the description column alone, but when I include
the date column in the formula it doesn't work.

The CSE formula reads as follows:

{=SUM(IF(($B$4:$B$245="Entertainment")*($A$4:$A$24 5="*Feb*"),$E$4:$E
$245,0))}

It doesn't work because the function bar doesn't say Feb, or February
- it reads 1/1/2008 so that one argument is consistently false and it
doesn't summarize - that much I know. The cells for this column are
formatted as Dates and the dates in the ledger must be displayed as
mmm,dd,yyyy - I don't have a choice in that.

In this case, how can I get the ledger to summarize only the rows that
are dated in February and to exclude those entered in January (for
example)?

Thanks in Advance,
Craig

Excel Formulas in General Ledger
 

Hi Craig

You need
*(MONTH($A$4:$A$245)=2)
or
*(TEXT($A$4:$A$245,"mmm")="Feb")

I think the non-array Sumproduct would be quicker in calculation

=SUMPRODUCT(($B$4:$B$245="Entertainment")*
(TEXT($A$4:$A$245,"mmm")="Feb")*$E$4:$E$245)



--
Regards
Roger Govier

wrote in message
...
Hi,

I am creating a Ledger that has to evaluate two columns - A
description column and a date column. If the result of both are true,
it populates another worksheet with the amount entered in a 3rd
column. In this case, both arguments must be true to have the figure
included in the summarization.

It works using only the description column alone, but when I include
the date column in the formula it doesn't work.

The CSE formula reads as follows:

{=SUM(IF(($B$4:$B$245="Entertainment")*($A$4:$A$24 5="*Feb*"),$E$4:$E
$245,0))}

It doesn't work because the function bar doesn't say Feb, or February
- it reads 1/1/2008 so that one argument is consistently false and it
doesn't summarize - that much I know. The cells for this column are
formatted as Dates and the dates in the ledger must be displayed as
mmm,dd,yyyy - I don't have a choice in that.

In this case, how can I get the ledger to summarize only the rows that
are dated in February and to exclude those entered in January (for
example)?

Thanks in Advance,
Craig

Excel Formulas in General Ledger
 

On Feb 7, 3:42 pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi Craig

You need
*(MONTH($A$4:$A$245)=2)
or
*(TEXT($A$4:$A$245,"mmm")="Feb")

I think the non-array Sumproduct would be quicker in calculation

=SUMPRODUCT(($B$4:$B$245="Entertainment")*
(TEXT($A$4:$A$245,"mmm")="Feb")*$E$4:$E$245)

--
Regards
Roger Govier

wrote in message

...

Hi,

I am creating a Ledger that has to evaluate two columns - A
description column and a date column. If the result of both are true,
it populates another worksheet with the amount entered in a 3rd
column. In this case, both arguments must be true to have the figure
included in the summarization.

It works using only the description column alone, but when I include
the date column in the formula it doesn't work.

The CSE formula reads as follows:

{=SUM(IF(($B$4:$B$245="Entertainment")*($A$4:$A$24 5="*Feb*"),$E$4:$E
$245,0))}

It doesn't work because the function bar doesn't say Feb, or February
- it reads 1/1/2008 so that one argument is consistently false and it
doesn't summarize - that much I know. The cells for this column are
formatted as Dates and the dates in the ledger must be displayed as
mmm,dd,yyyy - I don't have a choice in that.

In this case, how can I get the ledger to summarize only the rows that
are dated in February and to exclude those entered in January (for
example)?

Thanks in Advance,
Craig

Thanks very much Roger - the SUMPRODUCT works great!

Craig