Hello:

I have this below compare in a CELL...

=SUMPRODUCT((Clarity!$A$1:$A$3000=A27)*(Clarity!$B $1:$B$3000="January 2012"),Clarity!$E$1:$E$3000)

Everything works but I need to insert something other than a literal "2012" when the year flips to 2013 or I will have to update 12 CELLS for 17 people every year... is there a way to insert a MACRO for each of the twelve monthly CELLS, such as;

="January "& NOW(YEAR) or something different that works????

Thanks in advance

Hi,

Am Sat, 22 Dec 2012 17:25:51 +0000 schrieb FPerg:

=SUMPRODUCT((Clarity!$A$1:$A$3000=A27)*(Clarity!$B $1:$B$3000="January
2012"),Clarity!$E$1:$E$3000)

Everything works but I need to insert something other than a literal
"2012" when the year flips to 2013 or I will have to update 12 CELLS for
17 people every year... is there a way to insert a MACRO for each of the
twelve monthly CELLS, such as;

="January "& NOW(YEAR) or something different that works????

if you want a formula, try:
=SUMPRODUCT(--(Clarity!$A$1:$A$3000=A27),--(MONTH(Clarity!$B$1:$B$3000)=1),--(YEAR(Clarity!$B$1:$B$3000)=YEAR(TODAY())),Clarity !$E$1:$E$3000)
for January of the current year


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2