month lookup

[ciojr]

i have the following formula
=IF(MONTH($B:$B)=MONTH(J1),+SUMIF($H:$H,"auto",$F:$F)+SUMIF($H:
$H,"auto",$E:$E))

problem is i am getting results (sum) of all of column F and E
I only want the amounts in F and E if the applicable row is the month
of jan
B E F H
01/01/07 200.00 55.00 auto
01/15/07 201.00 0.00 auto
02/01/07 100.00 25.00 auto
02/15/07 125.00 35.00 milk


So for Jan - i should get 456.00


What am i missing here.

 

[T. Valko]

Try it like this:

=SUMPRODUCT(--(MONTH(B2:B5)=1),E2:E5+F2:F5,--(H2:H5="auto"))

A couple of notes.

If there are any empty cells in column B (within your range) then you need
to add another array since an empty cell will evaluate as month 1:

=SUMPRODUCT(--(ISNUMBER(B2:B5)),--(MONTH(B2:B5)=1),E2:E5+F2:F5,--(H2:H5="auto"))

Also, you can't use entire columns as range references directly with
SUMPRODUCT unless you're using Excel 2007.

 

[T. Valko]

Ooops!

I forgot about this:

.....=MONTH(J1)

=SUMPRODUCT(--(MONTH(B2:B5)=MONTH(J1)),E2:E5+F2:F5,--(H2:H5="auto"))

=SUMPRODUCT(--(ISNUMBER(B2:B5)),--(MONTH(B2:B5)=MONTH(J1)),E2:E5+F2:F5,--(H2:H5="auto"))

 

[ciojr]

Try it like this:

=SUMPRODUCT(--(MONTH(B2:B5)=1),E2:E5+F2:F5,--(H2:H5="auto"))

A couple of notes.

If there are any empty cells in column B (within your range) then you need
to add another array since an empty cell will evaluate as month 1:

=SUMPRODUCT(--(ISNUMBER(B2:B5)),--(MONTH(B2:B5)=1),E2:E5+F2:F5,--(H2:H5="au­to"))

Also, you can't use entire columns as range references directly with
SUMPRODUCT unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP









- Show quoted text -

This worked perfect. Thank you so much.

 

[T. Valko]

Try it like this:

=SUMPRODUCT(--(MONTH(B2:B5)=1),E2:E5+F2:F5,--(H2:H5="auto"))

A couple of notes.

If there are any empty cells in column B (within your range) then you need
to add another array since an empty cell will evaluate as month 1:

=SUMPRODUCT(--(ISNUMBER(B2:B5)),--(MONTH(B2:B5)=1),E2:E5+F2:F5,--(H2:H5="au­to"))

Also, you can't use entire columns as range references directly with
SUMPRODUCT unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP









- Show quoted text -

This worked perfect. Thank you so much.

You're welcome. Thanks for the feedback!