Excel Formulas in General Ledger

[craigwarmington]

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$245="*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

 

[Roger Govier]

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)

 

[craigwarmington]

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)

Thanks very much Roger - the SUMPRODUCT works great!

Craig