| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to count blank cells that contain formula? | Excel Discussion (Misc queries) | |||
| FORMULA NEEDED TO COUNT DAYS IN EXCEL BETWEEN DATES | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| what formula do I use to count right-justified cells in a row? | Excel Worksheet Functions | |||
| formula to count cells not blanK | Excel Worksheet Functions | |||
Linear Mode
