Formula to count cells between dates.

[Guest]

I need to count the over the last week of a specific P/N .
Then I need the quantity manufactured over the last 4 weeks and finally over
the current year. The spreadsheet data will be entered daily for the entire
year and reviewed on a weekly basis.

 

[Bob Phillips]

Current week

=SUMPRODUCT(--(A1:A100>=TODAY()-CHOOSE(WEEKDAY(TODAY()),-1,0,1,2,3,4,5)),--(
A1:A100<TODAY()-CHOOSE(WEEKDAY(TODAY()),-1,0,1,2,3,4,5)+7),B1:B100)

Last 4 weeks

=SUMPRODUCT(--(A1:A100>=TODAY()-CHOOSE(WEEKDAY(TODAY()),-1,0,1,2,3,4,5)-21),
--(A1:A100<TODAY()-CHOOSE(WEEKDAY(TODAY()),-1,0,1,2,3,4,5)+7),B1:B100)

entire year

=SUMPRODUCT(--(YEAR(A1:A100)=YEAR(TODAY())),B1:B100)


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Hi Bob,
I'm coming up with a sum of "0". Would it be a problem if the P/N's are
alpha numeric? Also, I wish to count only specific P/N in the specified
column. (In this sample "D")
In the attached sample below, I'm looking for the quantity in columns A,B,C
and D. Only in column "D" the cells may be blank and contain several
different P/N's.

Manufactured Date Assembly P/N Problem
Defective P/N
6/8/2006 A5020 Intermittent PCB
A5070
6/11/2006 A5050 No light - PCB problem
A5070
6/8/2006 A5020
6/8/2006 A5050 No light - PCB problem A5000
6/5/2006 A5020 Cannot turn off camera A5000
6/8/2006 A5020
6/8/2006 A5020
6/11/2006 A5050 No light - PCB problem A5070

 

[Bob Phillips]

Didn't anticipate Sunday dates <vbg>

Try

=SUMPRODUCT(--(A2:A10>=TODAY()-CHOOSE(WEEKDAY(TODAY()),0,1,2,3,4,5,6)),
--(A2:A10<TODAY()-CHOOSE(WEEKDAY(TODAY()),0,1,2,3,4,5,6)+7),--(D210="A5070
"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Thanks Bob....

Didn't anticipate Sunday dates <vbg>

Try

=SUMPRODUCT(--(A2:A10>=TODAY()-CHOOSE(WEEKDAY(TODAY()),0,1,2,3,4,5,6)),
--(A2:A10<TODAY()-CHOOSE(WEEKDAY(TODAY()),0,1,2,3,4,5,6)+7),--(D210="A5070
"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)