| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif between two dates based on another value
Hi,
I'm trying to count the number of occurances of a value between two dates that I specify. I've done this by using the following formula and it works: =COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C2) The dates are on sheet 'Analysis' in C1 and C2. The dates are in column E on sheet 'Pre-Sales & WIP'. Problem - Although this works I want it to only do the count when a value in column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example). I added an IF function to the beginning of my formula but this only told it to perform the calculation if column B contained the word "WIP" anywhere. I want it to count the values for every occurance of the word "WIP". eg: WIP - Between Correct Dates Specfied = Count (another word) - Between or Not Between Dates Specified = No Count I do hope this makes sense, any help appreciated! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif between two dates based on another value
You can only use COUNTIF (or SUMIF) if you have one condition. As you
have more, then try this: =SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales & WIP'!E1:E100<=Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP")) Note also that with SUMPRODUCT you can not use full column references (unless you have Excel 2007), so I have made these refer to 100 rows - change if you have more. I've assumed start date in C1 and end date in C2 in the Analysis sheet. Hope this helps. Pete On Feb 22, 11:22*am, Bradley Searle <Bradley wrote: Hi, I'm trying to count the number of occurances of a value between two dates that I specify. I've done this by using the following formula and it works: =COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C2) The dates are on sheet 'Analysis' in C1 and C2. *The dates are in column E on sheet 'Pre-Sales & WIP'. Problem - Although this works I want it to only do the count when a value in column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example). I added an IF function to the beginning of my formula but this only told it to perform the calculation if column B contained the word "WIP" anywhere. *I want it to count the values for every occurance of the word "WIP". eg: WIP - Between Correct Dates Specfied = Count (another word) - Between or Not Between Dates Specified = No Count I do hope this makes sense, any help appreciated! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif between two dates based on another value
Pete / David - Thank you so much for the quick reply! Your solutions worked
perfectly. Very impressed! "David Biddulph" wrote: =SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales & WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP")) -- David Biddulph "Bradley Searle" <Bradley wrote in message ... Hi, I'm trying to count the number of occurances of a value between two dates that I specify. I've done this by using the following formula and it works: =COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C2) The dates are on sheet 'Analysis' in C1 and C2. The dates are in column E on sheet 'Pre-Sales & WIP'. Problem - Although this works I want it to only do the count when a value in column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example). I added an IF function to the beginning of my formula but this only told it to perform the calculation if column B contained the word "WIP" anywhere. I want it to count the values for every occurance of the word "WIP". eg: WIP - Between Correct Dates Specfied = Count (another word) - Between or Not Between Dates Specified = No Count I do hope this makes sense, any help appreciated! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif between two dates based on another value
Glad to have been able to help.
-- David Biddulph "Bradley Searle" wrote in message ... Pete / David - Thank you so much for the quick reply! Your solutions worked perfectly. Very impressed! "David Biddulph" wrote: =SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales & WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP")) -- David Biddulph "Bradley Searle" <Bradley wrote in message ... Hi, I'm trying to count the number of occurances of a value between two dates that I specify. I've done this by using the following formula and it works: =COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C2) The dates are on sheet 'Analysis' in C1 and C2. The dates are in column E on sheet 'Pre-Sales & WIP'. Problem - Although this works I want it to only do the count when a value in column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example). I added an IF function to the beginning of my formula but this only told it to perform the calculation if column B contained the word "WIP" anywhere. I want it to count the values for every occurance of the word "WIP". eg: WIP - Between Correct Dates Specfied = Count (another word) - Between or Not Between Dates Specified = No Count I do hope this makes sense, any help appreciated! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif between two dates based on another value
Thanks for feeding back. Slight difference between the two formulae -
mine includes the end date whereas David's doesn't. Pete On Feb 22, 12:15*pm, Bradley Searle wrote: Pete / David - Thank you so much for the quick reply! *Your solutions worked perfectly. *Very impressed! "David Biddulph" wrote: =SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales & WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP")) -- David Biddulph "Bradley Searle" <Bradley wrote in message ... Hi, I'm trying to count the number of occurances of a value between two dates that I specify. I've done this by using the following formula and it works: =COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C2) The dates are on sheet 'Analysis' in C1 and C2. *The dates are in column E on sheet 'Pre-Sales & WIP'. Problem - Although this works I want it to only do the count when a value in column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example). I added an IF function to the beginning of my formula but this only told it to perform the calculation if column B contained the word "WIP" anywhere. I want it to count the values for every occurance of the word "WIP". eg: WIP - Between Correct Dates Specfied = Count (another word) - Between or Not Between Dates Specified = No Count I do hope this makes sense, any help appreciated!- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif between two dates based on another value
Yes. I'd tried to make my formula equivalent (in the date part) to the OP's
original formula. -- David Biddulph "Pete_UK" wrote in message ... Thanks for feeding back. Slight difference between the two formulae - mine includes the end date whereas David's doesn't. Pete On Feb 22, 12:15 pm, Bradley Searle wrote: Pete / David - Thank you so much for the quick reply! Your solutions worked perfectly. Very impressed! "David Biddulph" wrote: =SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales & WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP")) -- David Biddulph "Bradley Searle" <Bradley wrote in message ... Hi, I'm trying to count the number of occurances of a value between two dates that I specify. I've done this by using the following formula and it works: =COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C2) The dates are on sheet 'Analysis' in C1 and C2. The dates are in column E on sheet 'Pre-Sales & WIP'. Problem - Although this works I want it to only do the count when a value in column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example). I added an IF function to the beginning of my formula but this only told it to perform the calculation if column B contained the word "WIP" anywhere. I want it to count the values for every occurance of the word "WIP". eg: WIP - Between Correct Dates Specfied = Count (another word) - Between or Not Between Dates Specified = No Count I do hope this makes sense, any help appreciated!- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif between two dates based on another value
Understood, David - I was just pointing out to the OP that there was a
slight difference between the two formulae. Pete On Feb 22, 1:43*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Yes. *I'd tried to make my formula equivalent (in the date part) to the OP's original formula. -- David Biddulph "Pete_UK" wrote in message ... Thanks for feeding back. Slight difference between the two formulae - mine includes the end date whereas David's doesn't. Pete On Feb 22, 12:15 pm, Bradley Searle wrote: Pete / David - Thank you so much for the quick reply! Your solutions worked perfectly. Very impressed! "David Biddulph" wrote: =SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales & WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP")) -- David Biddulph "Bradley Searle" <Bradley wrote in message ... Hi, I'm trying to count the number of occurances of a value between two dates that I specify. I've done this by using the following formula and it works: =COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C2) The dates are on sheet 'Analysis' in C1 and C2. The dates are in column E on sheet 'Pre-Sales & WIP'. Problem - Although this works I want it to only do the count when a value in column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example). I added an IF function to the beginning of my formula but this only told it to perform the calculation if column B contained the word "WIP" anywhere. I want it to count the values for every occurance of the word "WIP". eg: WIP - Between Correct Dates Specfied = Count (another word) - Between or Not Between Dates Specified = No Count I do hope this makes sense, any help appreciated!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif and dates | Excel Worksheet Functions | |||
| SUMIF & COUNTIF based on dates | Excel Worksheet Functions | |||
| CountIF with dates | Excel Discussion (Misc queries) | |||
| Countif using dates | Excel Worksheet Functions | |||
| countif and dates | New Users to Excel | |||
Hybrid Mode
