date sumfi

[Antonio Atala]

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)

 

[Bob Phillips]

=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)

 

[Tom Ogilvy]

=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

 

[Antonyo]

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]

OK, just change 2005 to C20.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Antonyo]

This is a copy of what I need this to do

I Tank You Again

http://www.geocities.com/antonioata/Logo1.xls

 

[Bob Phillips]

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]

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

 

[Antonyo]

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

 

[Bob Phillips]

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]

This is part of the inventory if you like to take a look
http://www.geocities.com/antonioata/Logo1.xls

Thank You.

 

[Bob Phillips]

I did previously

--

HTH

RP
(remove nothere from the email address if mailing direct)