Criteria For SU<IF

[Minitman]

Greetings,

I am trying to get the sum of a column of numbers (column E) if the
date in column A is within the same month and year of the date in the
row this formula is sitting in. This sheet covers several years.

What I have so far is this formula sitting in E10 (the cell where I
want the sum to be):

=SUMIF($A:$A,$A10,E:E)

Row 10 is used only as a monthly divider and monthly subtotals (For
this example). Column A is formatted as date in the "mm dd, yy"
format.

With values in column E that should return a sum total, I am instead
getting "$0.00". My criteria ($A10) is not working.

How can I change the criteria to look at only for the "mm, yy" of
column A's "mm dd, yy" entries and not the full "mm dd, yy" date?

Any help will be greatly appreciated.

-Minitman

 

[Bob Phillips]

=SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100)

 

[Minitman]

Hey Bob,

Thanks for the reply.

I copied and pasted your code:

=SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100)

And ran a test.

It returned the #VALUE! error.

I tried to modify it like so:

=SUMPRODUCT(--(YEAR(A:A)=YEAR(A10)),--(MONTH(A:A)=MONTH(A10)),E:E)

And it returned the #NUM! error.

That column is formatted as currency, but that shouldn't make any
difference, should it?

What did I do wrong?

-Minitman

 

[Bob Phillips]

I don't know really, the formula works fine, I just re-tested it.

You can't use whole columns in SUMPRODUCT, unless you have Excel 2007, it
must be an explicit range.

 

[Shane Devenshire]

Hi,

First, you are comparing A10 to A10 since A10 is in the range A1:A100, is
this really what you want?

Second the formula will return an error is the dates in column A are not
Excel acceptable dates or if the entries in column E are formulas anyone of
which returns a VALUE error. Also the dates you are comparing the range to
must be a date, in my example below that is cell A10

=SUMPRODUCT(--(YEAR(B1:B100)=YEAR(A10)),--(MONTH(B1:B100)=MONTH(A10)),F1:F100)

I have tested the formula Bob gave you and it works fine if none of the
problems mentioned above are going on.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Minitman]

Hey Shane,

Thanks for the reply.

I think you got it right!!! My approach seems to be less then ideal.

I am trying to get monthly subtotals of 25 columns.

Do you have any suggestions as to a practical way to do this (it
appears that my idea was not very practical)?

Any help is appreciated.

-Minitman