Sumif help

[jmitton]

Hi,

i need some more help.

I have the following formula

=SUMIF('ACCOUNT BALANCE'!A:A,"/01/2013",'ACCOUNT BALANCE'!C:C)

the criteria i need is to search for the month and year, if i just use it like this it returns no value. is there any wild card that i can use to replace the date like ??/01/2013.

Jonathan

[bebo021999]

May be:
=SUMIFS(C:C,MONTH(A:A),1,YEAR(A:A),2013)

[Kevin UK]

Hi jmitton

Look into the SUMPRODUCT function.

[Tony Valko]

If your column A contains true Excel dates try this:

=SUMPRODUCT(--(TEXT('ACCOUNT BALANCE'!A2:A10,"mmmyyyy")="Jan2013"),'ACCOUNT BALANCE'!C2:C10)

You should avoid using entire columns as range references with the SUMPRODUCT function.

[Kevin UK]

You cant use the SUMIFS like that!

May be:
=SUMIFS(C:C,MONTH(A:A),1,YEAR(A:A),2013)

[oeldere]

Make a column with the formula's:

=month(a1)
=Year(a1)

after that you be able to use these values in a pivot table.

[Tony Valko]

May be:
=SUMIFS(C:C,MONTH(A:A),1,YEAR(A:A),2013)

That won't work.

With the SUMIF(S) function(s) you can only do "straight" comparisons. You can't test the range for the month or year.

[Teethless mama]

May be:
=SUMIFS(C:C,MONTH(A:A),1,YEAR(A:A),2013)

Try it like this:

=SUMIFS(C:C,A:A,">=2013-1-1",A:A,"<=2013-1-31")