Formula to count cells between dates.

V

Vegs

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.
 
B

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

Vegs

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
 
B

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),--(D2:D10="A5070
"))

--

HTH

Bob Phillips

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

Vegs

Thanks Bob....

Bob Phillips said:
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),--(D2:D10="A5070
"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Click to expand...
 
Top