Formula that looks only at dates which fall during the current calendar year (relatively speaking)

[Aimee S.]

Hello.

I have this formula which looks at dates that fall in the prior calendar month (relatively speaking). I will highlight that section in bold font:


=IFERROR(SUMPRODUCT(--('Metric Data #4'!$D$5:'Metric Data #4'!$D$7402=$P116),--(TEXT('Metric Data #4'!$E$5:'Metric Data #4'!$E$7402,"mmmyyyy")=TEXT(YEAR(TODAY()),"mmmyyyy"))),"n/a")


I want this same formula but with the part in bold to look for values not in the prior month only, but for the entire current calendar year, whatever that may be (currently 2011 but will change to 2012 Jan 1.

If i am not being fully clear let me know and thanks in advance for all your help!

Aimee

 

[Aladin Akyurek]

Looks like you want...

=IFERROR(
   SUMPRODUCT(
     --('Metric Data #4'!$D$5:$D$7402=$P116),
     --(YEAR('Metric Data #4'!$E$5:$E$7402)=YEAR(TODAY())),
   "n/a")

 

[Aimee S.]

Wicked! That works. Just had to add one more ) to make excel happy and voila! Thanks so much!

=IFERROR(SUMPRODUCT(--('Metric Data #4'!$D$5:$D$7402=$P116),--(YEAR('Metric Data #4'!$E$5:$E$7402)=YEAR(TODAY()))),"n/a")

 

[T. Valko]

Hello.

I have this formula which looks at dates that fall in the prior calendar month (relatively speaking). I will highlight that section in bold font:


=IFERROR(SUMPRODUCT(--('Metric Data #4'!$D$5:'Metric Data #4'!$D$7402=$P116),--(TEXT('Metric Data #4'!$E$5:'Metric Data #4'!$E$7402,"mmmyyyy")=TEXT(YEAR(TODAY()),"mmmyyyy"))),"n/a")


I want this same formula but with the part in bold to look for values not in the prior month only, but for the entire current calendar year, whatever that may be (currently 2011 but will change to 2012 Jan 1.

If i am not being fully clear let me know and thanks in advance for all your help!

Aimee

Try this...

=IFERROR(SUMPRODUCT(--('Metric Data #4'!$D$5:$D$7402=$P116),--(YEAR('Metric Data #4'!$E$5:$E$7402)=YEAR(NOW()))),"n/a")

 

[Aladin Akyurek]

Wicked! That works. Just had to add one more ) to make excel happy and voila! Thanks so much!

=IFERROR(SUMPRODUCT(--('Metric Data #4'!$D$5:$D$7402=$P116),--(YEAR('Metric Data #4'!$E$5:$E$7402)=YEAR(TODAY()))),"n/a")

Great. Thanks for providing feedback.