| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count If formula with week range
I am trying to write a count if formula to count the total in A1:A3 and count
it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If formula with week range
Hi,
try In D4 enter =sumproduct(--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: I am trying to write a count if formula to count the total in A1:A3 and count it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If formula with week range
Could not get it to work but keep in mind A:1 to A3 might not all be NBC so I
need it to count if it matches A4 and so on. "Eduardo" wrote: Hi, try In D4 enter =sumproduct(--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: I am trying to write a count if formula to count the total in A1:A3 and count it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If formula with week range
Hi,
sorry I didn';t take in consideration NBC change to =sumproduct(--(A4="NBC"),--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: Could not get it to work but keep in mind A:1 to A3 might not all be NBC so I need it to count if it matches A4 and so on. "Eduardo" wrote: Hi, try In D4 enter =sumproduct(--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: I am trying to write a count if formula to count the total in A1:A3 and count it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If formula with week range
Opps, use
=sumproduct(--(A1:A3="NBC"),--(D3B1:B3),--(D4<B1:B3),c1:c3) "Eduardo" wrote: Hi, sorry I didn';t take in consideration NBC change to =sumproduct(--(A4="NBC"),--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: Could not get it to work but keep in mind A:1 to A3 might not all be NBC so I need it to count if it matches A4 and so on. "Eduardo" wrote: Hi, try In D4 enter =sumproduct(--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: I am trying to write a count if formula to count the total in A1:A3 and count it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If formula with week range
I think we are on the correct path but am not sure you understand where I am
placing my data. Below is a better example. Sheet 1 is where the data is and sheet two I will type in NBC or CBX and I want it to place the total in the proper columns. The totals come from C in sheet one. Sheet 1 a b c 1 NBC 3/16/09 15.00 2 NBC 3/30/09 30.00 3 CBX 3/31/09 15.00 4 NBC 3/29/09 10.00 5 Sheet 2 a b c d 1 Week Start Week Start Week Start 2 3/2/09 3/16/09 3/30/09 3 NBC 25.00 30.00 4 CBX 15.00 5 6 7 "Eduardo" wrote: Hi, sorry I didn';t take in consideration NBC change to =sumproduct(--(A4="NBC"),--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: Could not get it to work but keep in mind A:1 to A3 might not all be NBC so I need it to count if it matches A4 and so on. "Eduardo" wrote: Hi, try In D4 enter =sumproduct(--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: I am trying to write a count if formula to count the total in A1:A3 and count it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If formula with week range
Hi,
yes the example clarify use =sumproduct(--($A3=sheet1!$A$1:$A$100),--(D2sheet1!$B$1:$B$100),--(D2<sheet1!$B$1:$B$100),sheet1!$c$1:$c$100) change the range to fit your needs but remember the range has to be the same in all parts of the formula "Jeremy" wrote: I think we are on the correct path but am not sure you understand where I am placing my data. Below is a better example. Sheet 1 is where the data is and sheet two I will type in NBC or CBX and I want it to place the total in the proper columns. The totals come from C in sheet one. Sheet 1 a b c 1 NBC 3/16/09 15.00 2 NBC 3/30/09 30.00 3 CBX 3/31/09 15.00 4 NBC 3/29/09 10.00 5 Sheet 2 a b c d 1 Week Start Week Start Week Start 2 3/2/09 3/16/09 3/30/09 3 NBC 25.00 30.00 4 CBX 15.00 5 6 7 "Eduardo" wrote: Hi, sorry I didn';t take in consideration NBC change to =sumproduct(--(A4="NBC"),--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: Could not get it to work but keep in mind A:1 to A3 might not all be NBC so I need it to count if it matches A4 and so on. "Eduardo" wrote: Hi, try In D4 enter =sumproduct(--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: I am trying to write a count if formula to count the total in A1:A3 and count it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If formula with week range
Opps I found a mistake use
=SUMPRODUCT(--($A3=Sheet1!$A$1:$A$100),--(C$2Sheet1!$B$1:$B$100),--(D$2<Sheet1!$B$1:$B$100),Sheet1!$C$1:$C$100) copy formula right and down "Eduardo" wrote: Hi, yes the example clarify use =sumproduct(--($A3=sheet1!$A$1:$A$100),--(D2sheet1!$B$1:$B$100),--(D2<sheet1!$B$1:$B$100),sheet1!$c$1:$c$100) change the range to fit your needs but remember the range has to be the same in all parts of the formula "Jeremy" wrote: I think we are on the correct path but am not sure you understand where I am placing my data. Below is a better example. Sheet 1 is where the data is and sheet two I will type in NBC or CBX and I want it to place the total in the proper columns. The totals come from C in sheet one. Sheet 1 a b c 1 NBC 3/16/09 15.00 2 NBC 3/30/09 30.00 3 CBX 3/31/09 15.00 4 NBC 3/29/09 10.00 5 Sheet 2 a b c d 1 Week Start Week Start Week Start 2 3/2/09 3/16/09 3/30/09 3 NBC 25.00 30.00 4 CBX 15.00 5 6 7 "Eduardo" wrote: Hi, sorry I didn';t take in consideration NBC change to =sumproduct(--(A4="NBC"),--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: Could not get it to work but keep in mind A:1 to A3 might not all be NBC so I need it to count if it matches A4 and so on. "Eduardo" wrote: Hi, try In D4 enter =sumproduct(--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: I am trying to write a count if formula to count the total in A1:A3 and count it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If formula with week range
Well we are close but if you add a Week Start in E2 on sheet 2 for 4/6 you
get the total of each name in A in E. We are close. "Eduardo" wrote: Opps I found a mistake use =SUMPRODUCT(--($A3=Sheet1!$A$1:$A$100),--(C$2Sheet1!$B$1:$B$100),--(D$2<Sheet1!$B$1:$B$100),Sheet1!$C$1:$C$100) copy formula right and down "Eduardo" wrote: Hi, yes the example clarify use =sumproduct(--($A3=sheet1!$A$1:$A$100),--(D2sheet1!$B$1:$B$100),--(D2<sheet1!$B$1:$B$100),sheet1!$c$1:$c$100) change the range to fit your needs but remember the range has to be the same in all parts of the formula "Jeremy" wrote: I think we are on the correct path but am not sure you understand where I am placing my data. Below is a better example. Sheet 1 is where the data is and sheet two I will type in NBC or CBX and I want it to place the total in the proper columns. The totals come from C in sheet one. Sheet 1 a b c 1 NBC 3/16/09 15.00 2 NBC 3/30/09 30.00 3 CBX 3/31/09 15.00 4 NBC 3/29/09 10.00 5 Sheet 2 a b c d 1 Week Start Week Start Week Start 2 3/2/09 3/16/09 3/30/09 3 NBC 25.00 30.00 4 CBX 15.00 5 6 7 "Eduardo" wrote: Hi, sorry I didn';t take in consideration NBC change to =sumproduct(--(A4="NBC"),--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: Could not get it to work but keep in mind A:1 to A3 might not all be NBC so I need it to count if it matches A4 and so on. "Eduardo" wrote: Hi, try In D4 enter =sumproduct(--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: I am trying to write a count if formula to count the total in A1:A3 and count it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If formula with week range
Hi Jeremy,
The problem in column E is that you will not have anything in column F, so in that case cut a portion of the formula as follow =SUMPRODUCT(--($A3=Sheet1!$A$1:$A$100),--(e$2Sheet1!$B$1:$B$100),Sheet1!$C$1:$C$100) "Jeremy" wrote: Well we are close but if you add a Week Start in E2 on sheet 2 for 4/6 you get the total of each name in A in E. We are close. "Eduardo" wrote: Opps I found a mistake use =SUMPRODUCT(--($A3=Sheet1!$A$1:$A$100),--(C$2Sheet1!$B$1:$B$100),--(D$2<Sheet1!$B$1:$B$100),Sheet1!$C$1:$C$100) copy formula right and down "Eduardo" wrote: Hi, yes the example clarify use =sumproduct(--($A3=sheet1!$A$1:$A$100),--(D2sheet1!$B$1:$B$100),--(D2<sheet1!$B$1:$B$100),sheet1!$c$1:$c$100) change the range to fit your needs but remember the range has to be the same in all parts of the formula "Jeremy" wrote: I think we are on the correct path but am not sure you understand where I am placing my data. Below is a better example. Sheet 1 is where the data is and sheet two I will type in NBC or CBX and I want it to place the total in the proper columns. The totals come from C in sheet one. Sheet 1 a b c 1 NBC 3/16/09 15.00 2 NBC 3/30/09 30.00 3 CBX 3/31/09 15.00 4 NBC 3/29/09 10.00 5 Sheet 2 a b c d 1 Week Start Week Start Week Start 2 3/2/09 3/16/09 3/30/09 3 NBC 25.00 30.00 4 CBX 15.00 5 6 7 "Eduardo" wrote: Hi, sorry I didn';t take in consideration NBC change to =sumproduct(--(A4="NBC"),--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: Could not get it to work but keep in mind A:1 to A3 might not all be NBC so I need it to count if it matches A4 and so on. "Eduardo" wrote: Hi, try In D4 enter =sumproduct(--(D3B1:B100),--(D4<B1:B100),c1:c100) "Jeremy" wrote: I am trying to write a count if formula to count the total in A1:A3 and count it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the date given in D2 and on. Examples below Thank you for the help A B C 1 NBC 4/20/09 7865.42 2 NBC 3/28/09 1268.42 3 NBC 4/22/09 1000.00 A D E F 1 Week Start Week Start Week Start 2 3/30/09 4/13/09 4/27/09 4 NBC 1268.42 8865.42 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula to count charcter in a range | Excel Worksheet Functions | |||
| How t count by week whatever is with in the week (two criteria, ma | Excel Discussion (Misc queries) | |||
| Count formula within a named range. | Excel Discussion (Misc queries) | |||
| Help with week day count with range | Excel Worksheet Functions | |||
| Formula to count only positives in range | Excel Discussion (Misc queries) | |||
Linear Mode
