Sum items occurring in a month and year

[Saylindara]

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)))

 

[Jim Thomlinson]

Take a look at SumProduct instead...

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

 

[Paul C]

In Excel 2007 the SUMIFS function will allow multiple criteria.

You can also use a conditional SUMPRODUCT like this
=sumproduct(--(D410002=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 (--(d410002=Referrals
List!$a$5),--(month(J4:J10002)=Month($b$2),--(Year(j4:j10002)=Year($b$2),
R4:R10002)

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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