| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date sumfi
How do I add to this formula to look only for year 2005
=SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
=SUMPRODUCT(--(YEAR(S!$M$5:$M$181)=2005),S!$D$5:$D$181)
and =SUMPRODUCT(--(YEAR(S!$M$4:$M$103)=2005)) -- HTH RP (remove nothere from the email address if mailing direct) "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
=SUMIF(S!$M$5:$M$181,"Dec 31,
2004",S!$D$5:$D$181)-SUMIF(S!$M$5:$M$181,"Dec 31, 2005",S!$D$5:$D$181) Similar for CountIf or =sumproduct(--(Year(S!$M$5:$M$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$M$5:$M$181)=2005)) for countif -- Regards, Tom Ogilvy "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
Bob, Tom,
Thank You For your answer the code needs to have ,C20, C20 is the item wich will be look for in S!$M$5:$M$181 ThankYou "Bob Phillips" escribió en el mensaje ... =SUMPRODUCT(--(YEAR(S!$M$5:$M$181)=2005),S!$D$5:$D$181) and =SUMPRODUCT(--(YEAR(S!$M$4:$M$103)=2005)) -- HTH RP (remove nothere from the email address if mailing direct) "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
OK, just change 2005 to C20.
-- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... Bob, Tom, Thank You For your answer the code needs to have ,C20, C20 is the item wich will be look for in S!$M$5:$M$181 ThankYou "Bob Phillips" escribió en el mensaje ... =SUMPRODUCT(--(YEAR(S!$M$5:$M$181)=2005),S!$D$5:$D$181) and =SUMPRODUCT(--(YEAR(S!$M$4:$M$103)=2005)) -- HTH RP (remove nothere from the email address if mailing direct) "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
This is a copy of what I need this to do
I Tank You Again http://www.geocities.com/antonioata/Logo1.xls "Tom Ogilvy" escribió en el mensaje ... =SUMIF(S!$M$5:$M$181,"Dec 31, 2004",S!$D$5:$D$181)-SUMIF(S!$M$5:$M$181,"Dec 31, 2005",S!$D$5:$D$181) Similar for CountIf or =sumproduct(--(Year(S!$M$5:$M$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$M$5:$M$181)=2005)) for countif -- Regards, Tom Ogilvy "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
So you want
=sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif -- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... This is a copy of what I need this to do I Tank You Again http://www.geocities.com/antonioata/Logo1.xls "Tom Ogilvy" escribió en el mensaje ... =SUMIF(S!$M$5:$M$181,"Dec 31, 2004",S!$D$5:$D$181)-SUMIF(S!$M$5:$M$181,"Dec 31, 2005",S!$D$5:$D$181) Similar for CountIf or =sumproduct(--(Year(S!$M$5:$M$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$M$5:$M$181)=2005)) for countif -- Regards, Tom Ogilvy "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
If I use
=sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif The specific item which is in the firth line will not be read, instead all the items in the year will be read =COUNTIF(S!$M$4:$M$874,C13) This will read the specific item but for every year in the data base S, C13 is the item alacena C14 is the item box individual and so on I have been breaking my head for almost 3 months I do not know if some one has a solution to this puzzle "Bob Phillips" escribió en el mensaje ... So you want =sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif -- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... This is a copy of what I need this to do I Tank You Again http://www.geocities.com/antonioata/Logo1.xls "Tom Ogilvy" escribió en el mensaje ... =SUMIF(S!$M$5:$M$181,"Dec 31, 2004",S!$D$5:$D$181)-SUMIF(S!$M$5:$M$181,"Dec 31, 2005",S!$D$5:$D$181) Similar for CountIf or =sumproduct(--(Year(S!$M$5:$M$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$M$5:$M$181)=2005)) for countif -- Regards, Tom Ogilvy "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
I'm Sorry what I was trying to say is
instead all the items for all years will be read If I use =sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif The specific item which is in the firth line will not be read, instead all the items for all years will be read =COUNTIF(S!$M$4:$M$874,C13) This will read the specific item but for every year in the data base S, C13 is the item alacena C14 is the item box individual and so on I have been breaking my head for almost 3 months I do not know if some one has a solution to this puzzle "Bob Phillips" escribió en el mensaje ... So you want =sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif -- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... This is a copy of what I need this to do I Tank You Again http://www.geocities.com/antonioata/Logo1.xls "Tom Ogilvy" escribió en el mensaje ... =SUMIF(S!$M$5:$M$181,"Dec 31, 2004",S!$D$5:$D$181)-SUMIF(S!$M$5:$M$181,"Dec 31, 2005",S!$D$5:$D$181) Similar for CountIf or =sumproduct(--(Year(S!$M$5:$M$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$M$5:$M$181)=2005)) for countif -- Regards, Tom Ogilvy "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
The biggest puzzle here is trying to understand what you mean and what you
want. The formula obviously won't count an item for Nov-2003 as it is testing for 2005. The formulas you have been supplied test against 2005 because that is what you asked for earlier. And I have no idea what alacena means. -- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... If I use =sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif The specific item which is in the firth line will not be read, instead all the items in the year will be read =COUNTIF(S!$M$4:$M$874,C13) This will read the specific item but for every year in the data base S, C13 is the item alacena C14 is the item box individual and so on I have been breaking my head for almost 3 months I do not know if some one has a solution to this puzzle "Bob Phillips" escribió en el mensaje ... So you want =sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif -- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... This is a copy of what I need this to do I Tank You Again http://www.geocities.com/antonioata/Logo1.xls "Tom Ogilvy" escribió en el mensaje ... =SUMIF(S!$M$5:$M$181,"Dec 31, 2004",S!$D$5:$D$181)-SUMIF(S!$M$5:$M$181,"Dec 31, 2005",S!$D$5:$D$181) Similar for CountIf or =sumproduct(--(Year(S!$M$5:$M$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$M$5:$M$181)=2005)) for countif -- Regards, Tom Ogilvy "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
This is part of the inventory if you like to take a look
http://www.geocities.com/antonioata/Logo1.xls Thank You. "Bob Phillips" escribió en el mensaje ... The biggest puzzle here is trying to understand what you mean and what you want. The formula obviously won't count an item for Nov-2003 as it is testing for 2005. The formulas you have been supplied test against 2005 because that is what you asked for earlier. And I have no idea what alacena means. -- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... If I use =sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif The specific item which is in the firth line will not be read, instead all the items in the year will be read =COUNTIF(S!$M$4:$M$874,C13) This will read the specific item but for every year in the data base S, C13 is the item alacena C14 is the item box individual and so on I have been breaking my head for almost 3 months I do not know if some one has a solution to this puzzle "Bob Phillips" escribió en el mensaje ... So you want =sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif -- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... This is a copy of what I need this to do I Tank You Again http://www.geocities.com/antonioata/Logo1.xls "Tom Ogilvy" escribió en el mensaje ... =SUMIF(S!$M$5:$M$181,"Dec 31, 2004",S!$D$5:$D$181)-SUMIF(S!$M$5:$M$181,"Dec 31, 2005",S!$D$5:$D$181) Similar for CountIf or =sumproduct(--(Year(S!$M$5:$M$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$M$5:$M$181)=2005)) for countif -- Regards, Tom Ogilvy "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date sumfi
I did previously
-- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... This is part of the inventory if you like to take a look http://www.geocities.com/antonioata/Logo1.xls Thank You. "Bob Phillips" escribió en el mensaje ... The biggest puzzle here is trying to understand what you mean and what you want. The formula obviously won't count an item for Nov-2003 as it is testing for 2005. The formulas you have been supplied test against 2005 because that is what you asked for earlier. And I have no idea what alacena means. -- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... If I use =sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif The specific item which is in the firth line will not be read, instead all the items in the year will be read =COUNTIF(S!$M$4:$M$874,C13) This will read the specific item but for every year in the data base S, C13 is the item alacena C14 is the item box individual and so on I have been breaking my head for almost 3 months I do not know if some one has a solution to this puzzle "Bob Phillips" escribió en el mensaje ... So you want =sumproduct(--(Year(S!$C$5:$C$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$C$5:$C$181)=2005)) for countif -- HTH RP (remove nothere from the email address if mailing direct) "Antonyo" wrote in message ... This is a copy of what I need this to do I Tank You Again http://www.geocities.com/antonioata/Logo1.xls "Tom Ogilvy" escribió en el mensaje ... =SUMIF(S!$M$5:$M$181,"Dec 31, 2004",S!$D$5:$D$181)-SUMIF(S!$M$5:$M$181,"Dec 31, 2005",S!$D$5:$D$181) Similar for CountIf or =sumproduct(--(Year(S!$M$5:$M$181)=2005),S!$D$5:$D$181) for sumif =sumproduct(--(Year(S!$M$5:$M$181)=2005)) for countif -- Regards, Tom Ogilvy "Antonio Atala" wrote in message ... How do I add to this formula to look only for year 2005 =SUMIF(S!$M$5:$M$181,C20,S!$D$5:$D$181) =COUNTIF(S!$M$4:$M$1003,C20) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Concatenate including a date so that the date appears as a date | Excel Worksheet Functions | |||
| date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
| Making a date go red, if date passes todays date. | Excel Worksheet Functions | |||
| Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
| Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Linear Mode
