ExcelBanter - Referring to a Date in SumIf

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Referring to a Date in SumIf (https://www.excelbanter.com/excel-programming/337940-referring-date-sumif.html)

Referring to a Date in SumIf

I am very new to forumlas/excel. I want a cell to sum C3:C100, if A3:A100
contains an entry for September. cells A3:A100 are formatted as dates:
09/01/2005.

I don't know how to fill in the blank in the following formula to tell the
worksheet to only sum entries for the month of September:

=SUMIF(A3:A100,_________,C3:C100)

How do I refer to "90" in the criteria section of this formula?

TIA
S. Jackson

Referring to a Date in SumIf

I finally figured it out:
=SUMPRODUCT((MONTH(A3:A100)=9)*C3:C100)

But, I was wondering if there was a way to use the SUMIF function. What is
the proper syntax?

Shelly

"S Jackson" wrote in message
...
I'm having trouble applying this. I tried this and got an error that said
the formula contained an error:

SUMIF((MONTH(A3:A100)=9),C3:C100)

What is wrong here?

S. Jackson

"Don Guillett" wrote in message
...
checksa is a defined name I gave to a range in columna. You may prefer
to
use
a2:a200 d2:d200
instead
change the 1 to 9 for sep

--
Don Guillett
SalesAid Software

"S Jackson" wrote in message
...
I'm sorry, but I'm going to need a little more help. I don't
understand.

FYI, column A is formatted mm/dd/yyyy, if that means anything.

"Don Guillett" wrote in message
...
try this one that I use
=SUMPRODUCT((MONTH(ChecksA)=1)*ChecksD)

--
Don Guillett
SalesAid Software

"S Jackson" wrote in message
...
I am very new to forumlas/excel. I want a cell to sum C3:C100, if
A3:A100
contains an entry for September. cells A3:A100 are formatted as
dates:
09/01/2005.

I don't know how to fill in the blank in the following formula to
tell
the
worksheet to only sum entries for the month of September:

=SUMIF(A3:A100,_________,C3:C100)

How do I refer to "90" in the criteria section of this formula?

TIA
S. Jackson

Referring to a Date in SumIf

=Sumif() wouldn't work on this.

You could insert another column that returns the month and use that:
=SUMIF(B1:B100,9,C1:C100)

or you could use an array formula:
=SUM(IF(MONTH(A1:A100)=9,C1:C100))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But =sumproduct() is easier to type and doesn't require the curly brackets.

But be aware that if you're looking for January figures, that empty cells will
look like they have months of 1 (January).

You may want:
=sumproduct(--(isnumber(a3:a100)),--(month(a3:a100)=1),c3:c100)

S Jackson wrote:

I finally figured it out:
=SUMPRODUCT((MONTH(A3:A100)=9)*C3:C100)

But, I was wondering if there was a way to use the SUMIF function. What is
the proper syntax?

Shelly

"S Jackson" wrote in message
...
I'm having trouble applying this. I tried this and got an error that said
the formula contained an error:

SUMIF((MONTH(A3:A100)=9),C3:C100)

What is wrong here?

S. Jackson

"Don Guillett" wrote in message
...
checksa is a defined name I gave to a range in columna. You may prefer
to
use
a2:a200 d2:d200
instead
change the 1 to 9 for sep

--
Don Guillett
SalesAid Software

"S Jackson" wrote in message
...
I'm sorry, but I'm going to need a little more help. I don't
understand.

FYI, column A is formatted mm/dd/yyyy, if that means anything.

"Don Guillett" wrote in message
...
try this one that I use
=SUMPRODUCT((MONTH(ChecksA)=1)*ChecksD)

--
Don Guillett
SalesAid Software

"S Jackson" wrote in message
...
I am very new to forumlas/excel. I want a cell to sum C3:C100, if
A3:A100
contains an entry for September. cells A3:A100 are formatted as
dates:
09/01/2005.

I don't know how to fill in the blank in the following formula to
tell
the
worksheet to only sum entries for the month of September:

=SUMIF(A3:A100,_________,C3:C100)

How do I refer to "90" in the criteria section of this formula?

TIA
S. Jackson

--

Dave Peterson