Standard sumif formula not recognised

[Loan]

Hi,

My excel does not recognise a standard function such as
=sumif(a1:a10,month(a10),b1:b10) or =eomonth(a1), whereby column A contains
dates and column b numbers.

Please help.

Thanks,
Loan

 

[byundt]

Loan,
Your formula was trying to compare a date/time serial number (3/23/05 is
38434) with a number like 1 through 12 (as returned by MONTH function). In
such a case, SUMIF should return 0.

If A1:A10 contain date/time serial numbers, you can add up the values in
column B for a given month with formulas like:
=SUMIF(A1:A10,">=" & DATEVALUE("1/1/05"),B1:B10)-SUMIF(A1:A10,">" &
DATEVALUE("1/31/05"),B1:B10) same month and year
=SUMPRODUCT((MONTH(A1:A10)=MONTH(A10))*B1:B10) same month, maybe not
same year
=SUMPRODUCT((MONTH(A1:A10)=MONTH(A10))*(YEAR(A1:A10)=YEAR(A10))*B1:B10)
same month and year
You'll need to format the results as a number, because Excel thinks you
intend it to be a date.

You can get the sum of values in column B for column A equalling the end of
a given month with:
=SUMIF(A1:A10,EOMONTH(A1,0),B1:B10)

 

[Biff]

Hi!

=sumif(a1:a10,month(a10),b1:b10)

Assume the date in A10 8/22/2005

Here's what the formula evaluates to:

=sumif(a1:a10,8,b1:b10)

If A1:A10 does not contain a specific value of 8 then the
formula fails (returns zero).

Try this instead:

=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(MONTH(A1:A10)=MONTH
(A10)),B1:B10)

=eomonth(a1)

EOMONTH requires 2 arguments. The first argument is the
Start_Date. The second argument is Months before or after
the Start_Date.

For example:

A10 = 8/22/2005

=EOMONTH(A10,0) = 8/31/2005
=EOMONTH(A10,1) = 9/30/2005
=EOMONTH(A10,-1) = 7/31/2005

Also, EOMONTH requires the Analysis ToolPak add-in be
installed. If after you add the second argument and get a
#NAME? error that means the ATP is probably not installed.

Biff