Year as well as month in formula

[Saylindara]

How can I include the year in this formula?

SUM(IF([Referrals10.xls]Referrals!$D$4:$D$250='[Referrals10.xls]Referral
Source'!$A5,IF([Referrals10.xls]Referrals!$J$4:$J$250=MONTH(B$2),1,0)))

 

[T. Valko]

Are there DATES in this range:

[Referrals10.xls]Referrals!$J$4:$J$250

Are you wanting to compare the month of the date in
[Referrals10.xls]Referrals!$J$4:$J$250 to the month of the date in B2? If
so, then this is incorrect:

IF([Referrals10.xls]Referrals!$J$4:$J$250=MONTH(B$2)

It should be:

IF(MONTH([Referrals10.xls]Referrals!$J$4:$J$250)=MONTH(B$2)

To include a test for the year number:

IF(YEAR([Referrals10.xls]Referrals!$J$4:$J$250)=YEAR(B$2)

You can do this without the array SUM(IF...

Normally entered:

=SUMPRODUCT(--([Referrals10.xls]Referrals!$D$4:$D$250='[Referrals10.xls]Referral
Source'!$A5),--(MONTH([Referrals10.xls]Referrals!$J$4:$J$250)=MONTH(B$2)),--(YEAR([Referrals10.xls]Referrals!$J$4:$J$250)=YEAR(B$2)))

 

[Saylindara]

Thank you for your very quick and helpful response.

Are there DATES in this range:

[Referrals10.xls]Referrals!$J$4:$J$250

Are you wanting to compare the month of the date in
[Referrals10.xls]Referrals!$J$4:$J$250 to the month of the date in B2? If
so, then this is incorrect:

IF([Referrals10.xls]Referrals!$J$4:$J$250=MONTH(B$2)

It should be:

IF(MONTH([Referrals10.xls]Referrals!$J$4:$J$250)=MONTH(B$2)

To include a test for the year number:

IF(YEAR([Referrals10.xls]Referrals!$J$4:$J$250)=YEAR(B$2)

You can do this without the array SUM(IF...

Normally entered:

=SUMPRODUCT(--([Referrals10.xls]Referrals!$D$4:$D$250='[Referrals10.xls]Referral
Source'!$A5),--(MONTH([Referrals10.xls]Referrals!$J$4:$J$250)=MONTH(B$2)),--(YEAR([Referrals10.xls]Referrals!$J$4:$J$250)=YEAR(B$2)))

--
Biff
Microsoft Excel MVP

How can I include the year in this formula?

SUM(IF([Referrals10.xls]Referrals!$D$4:$D$250='[Referrals10.xls]Referral
Source'!$A5,IF([Referrals10.xls]Referrals!$J$4:$J$250=MONTH(B$2),1,0)))

.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thank you for your very quick and helpful response.

Are there DATES in this range:

[Referrals10.xls]Referrals!$J$4:$J$250

Are you wanting to compare the month of the date in
[Referrals10.xls]Referrals!$J$4:$J$250 to the month of the date in B2? If
so, then this is incorrect:

IF([Referrals10.xls]Referrals!$J$4:$J$250=MONTH(B$2)

It should be:

IF(MONTH([Referrals10.xls]Referrals!$J$4:$J$250)=MONTH(B$2)

To include a test for the year number:

IF(YEAR([Referrals10.xls]Referrals!$J$4:$J$250)=YEAR(B$2)

You can do this without the array SUM(IF...

Normally entered:

=SUMPRODUCT(--([Referrals10.xls]Referrals!$D$4:$D$250='[Referrals10.xls]Referral
Source'!$A5),--(MONTH([Referrals10.xls]Referrals!$J$4:$J$250)=MONTH(B$2)),--(YEAR([Referrals10.xls]Referrals!$J$4:$J$250)=YEAR(B$2)))

--
Biff
Microsoft Excel MVP

How can I include the year in this formula?

SUM(IF([Referrals10.xls]Referrals!$D$4:$D$250='[Referrals10.xls]Referral
Source'!$A5,IF([Referrals10.xls]Referrals!$J$4:$J$250=MONTH(B$2),1,0)))

.