New Year

[Guest]

I have the below formulas that sum only months. With the New Year 2006 upon
us, what do I need to add in order for them to look at month and year?

=SUMPRODUCT(--(MONTH(Overall!K8:K1003)=8))

=SUMPRODUCT(--(MONTH(Overall!K8:K1003)=MONTH(J1)),--(ISNUMBER(SEARCH(H6,Overall!N8:N1003))))

=SUMPRODUCT(--(TEXT(Overall!T8:T1008,"mmm")=TEXT(J1,"mmm")))

 

[Bob Phillips]

=SUMPRODUCT(--(YEAR(Overall!K8:K1003)=2006),--(MONTH(Overall!K8:K1003)=8))

or

=SUMPRODUCT(--(TEXT(Overall!K8:K1003)="yyyymm")="200608"))

or

=SUMPRODUCT(--(Overall!K8:K1003-DAY(Overall!K8:K1003)+1=--"2006-08-01"))

--

HTH

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

 

[Roger Govier]

Hi Roy

=SUMPRODUCT(--(MONTH(Overall!K8:K1003)=8),--(YEAR(Overall!K8:K1003)=2006))


=SUMPRODUCT(--(MONTH(Overall!K8:K1003)=MONTH(J1)),--(YEAR(Overall!K8:K1003)=YEAR(J1)),--(ISNUMBER(SEARCH(H6,Overall!N8:N1003))))

=SUMPRODUCT(--(TEXT(Overall!T8:T1008,"yyyymmm")=TEXT(J1,"yyyymmm")))

Try

Regards

Roger Govier

 

[Guest]

Thanks.

=SUMPRODUCT(--(YEAR(Overall!K8:K1003)=2006),--(MONTH(Overall!K8:K1003)=8))

or

=SUMPRODUCT(--(TEXT(Overall!K8:K1003)="yyyymm")="200608"))

or

=SUMPRODUCT(--(Overall!K8:K1003-DAY(Overall!K8:K1003)+1=--"2006-08-01"))

--

HTH

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