|
Formula to count cells between dates.
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. |
Formula to count cells between dates.
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) "Vegs" wrote in message ... 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. |
Formula to count cells between dates.
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" wrote: 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) "Vegs" wrote in message ... 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. |
Formula to count cells between dates.
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) "Vegs" wrote in message ... 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" wrote: 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) "Vegs" wrote in message ... 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. |
Formula to count cells between dates.
Thanks Bob....
"Bob Phillips" wrote: 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) "Vegs" wrote in message ... 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" wrote: 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) "Vegs" wrote in message ... 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. |
| All times are GMT +1. The time now is 09:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com