I have a formula that uses this { } but can't duplicate it
 

I'm trying to add numbers in a column where the month of the date in an
adjacent column matches the month of a date in a criteria cell. I can't get
"SUMIF" to work. I had a formula that started and ended with this sign {}
but can't remember how I entered it.

I have a formula that uses this { } but can't duplicate it
 

The squiggly brackets denote an array but from your description you don't
need an array formula.

A1:A10 = dates
B1:B10 = numeric values

To sum column B where the corresponding cell in column A has a date in the
month of August:

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

For other months just change =8 to whatever month number you want. January
=1 through December =12.

--
Biff
Microsoft Excel MVP


"Stuart" wrote in message
...
I'm trying to add numbers in a column where the month of the date in an
adjacent column matches the month of a date in a criteria cell. I can't
get
"SUMIF" to work. I had a formula that started and ended with this sign {}
but can't remember how I entered it.

I have a formula that uses this { } but can't duplicate it
 

Just to complete the answer...

You get {} around the formula if it is an Array formula - press
CTRL-SHIFT-ENTER together to enter an array formula

"T. Valko" wrote:

The squiggly brackets denote an array but from your description you don't
need an array formula.

A1:A10 = dates
B1:B10 = numeric values

To sum column B where the corresponding cell in column A has a date in the
month of August:

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

For other months just change =8 to whatever month number you want. January
=1 through December =12.

--
Biff
Microsoft Excel MVP


"Stuart" wrote in message
...
I'm trying to add numbers in a column where the month of the date in an
adjacent column matches the month of a date in a criteria cell. I can't
get
"SUMIF" to work. I had a formula that started and ended with this sign {}
but can't remember how I entered it.

I have a formula that uses this { } but can't duplicate it
 

Hi,

Just to finish off your question -

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

Where D1 is the critieria cell.

And you could write this as

=SUM((MONTH(A1:A10)=MONTH(D1))*B1:B10)

In which case you would press Shift+Ctrl+Enter to enter it and you would see
the {}'s.

If this helps, please click the Yes button

cheers,
Shane Devenshire

"Stuart" wrote:

I'm trying to add numbers in a column where the month of the date in an
adjacent column matches the month of a date in a criteria cell. I can't get
"SUMIF" to work. I had a formula that started and ended with this sign {}
but can't remember how I entered it.