I've got the following formula to sum referrals received in a particular month.

Is it possible to make it specific to the year as well as the month?

=SUM(IF(Referrals!$D$4:$D$10002='Referrals
List'!$A5,IF(Referrals!$J$4:$J$10002=MONTH($B$2),1 ,0)))

Take a look at SumProduct instead...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Saylindara" wrote:

I've got the following formula to sum referrals received in a particular month.

Is it possible to make it specific to the year as well as the month?

=SUM(IF(Referrals!$D$4:$D$10002='Referrals
List'!$A5,IF(Referrals!$J$4:$J$10002=MONTH($B$2),1 ,0)))

In Excel 2007 the SUMIFS function will allow multiple criteria.

You can also use a conditional SUMPRODUCT like this
=sumproduct(--(D4:D10002=Referrals List!$A$5),--(j4:J10002=Month($b$2)),"ADD
YEAR HERE,ADD DATA RANGE TO SUM).

This would effectively add all column R totals whenever the referral in D
mathces the target and the Date in J matches the Month and Year target)

=sumproduct (--(d4:D10002=Referrals
List!$a$5),--(month(J4:J10002)=Month($b$2),--(Year(j4:j10002)=Year($b$2),
R4:R10002)
--
If this helps, please remember to click yes.

=SUM(IF(Referrals!$D$4:$D$10002='Referrals
List'!$A5,IF(Referrals!$J$4:$J$10002=MONTH($B$2),1 ,0)))

"Saylindara" wrote:

I've got the following formula to sum referrals received in a particular month.

Is it possible to make it specific to the year as well as the month?

=SUM(IF(Referrals!$D$4:$D$10002='Referrals
List'!$A5,IF(Referrals!$J$4:$J$10002=MONTH($B$2),1 ,0)))

=SUM(IF(Referrals!$D$4:$D$10002='Referrals List'!$A5,
IF(TEXT(Referrals!$J$4:$J$10002,"yyyymm")=TEXT($B$ 2,"yyyymm"),1,0)))

(still an array formula)


Or a non-array entered formula:
=SUMPRODUCT(--(Referrals!$D$4:$D$10002='Referrals List'!$A5),
--(TEXT(Referrals!$J$4:$J$10002,"yyyymm")=TEXT($B$2, "yyyymm")))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Saylindara wrote:

I've got the following formula to sum referrals received in a particular month.

Is it possible to make it specific to the year as well as the month?

=SUM(IF(Referrals!$D$4:$D$10002='Referrals
List'!$A5,IF(Referrals!$J$4:$J$10002=MONTH($B$2),1 ,0)))

--

Dave Peterson