|
Year as well as month in formula
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))) |
Year as well as month in formula
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 "Saylindara" wrote in message ... 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))) |
Year as well as month in formula
Thank you for your very quick and helpful response.
"T. Valko" wrote: 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 "Saylindara" wrote in message ... 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))) . |
Year as well as month in formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Saylindara" wrote in message ... Thank you for your very quick and helpful response. "T. Valko" wrote: 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 "Saylindara" wrote in message ... 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))) . |
| All times are GMT +1. The time now is 10:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com