| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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))) . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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))) . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
| Date formula where month and day remain the same, but year will va | Excel Worksheet Functions | |||
| To detect month or days or year in if formula | Excel Discussion (Misc queries) | |||
| convert number to month and year in formula | Excel Worksheet Functions | |||
| trying to get day/month/year froamt while user enters year only | New Users to Excel | |||
Linear Mode
