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

S

Stuart

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.
 
T

T. Valko

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.
 
S

Sheeloo

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
 
S

Shane Devenshire

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
 
Top