If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
possible countif formula?
Hi Again everyone
I am trying to get a formula in (possible countif) which doesn't seem to be working In column D I have the following dates D42 30/03/2009 D43 01/06/2005 D44 06/02/2009 D45 30/03/2009 D46 19/10/2009 D47 08/03/2010 D48 15/07/2010 D49 15/07/2010 D50 15/09/2010 D51 15/09/2010 In I3 I have 31/01/10, J3 28/02/10 etc etc running along to AR3 which has 31/12/12 I want to write a formula that simply says to check the dates in column D42 - D51 against the date in I3 and if the date in D42-51 is prior to I3 add 1, So my first forumla for January should = 5, February = 5, March 6 etc tec I have tried a countif forumula but it doesn't seem to work. Any ideas appreciated. Thank you |
#2
|
|||
|
|||
possible countif formula?
Hi,
Try this formula and copy to the right =countif($D$42:$D$51,"="&I$3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mark D" wrote in message ... Hi Again everyone I am trying to get a formula in (possible countif) which doesn't seem to be working In column D I have the following dates D42 30/03/2009 D43 01/06/2005 D44 06/02/2009 D45 30/03/2009 D46 19/10/2009 D47 08/03/2010 D48 15/07/2010 D49 15/07/2010 D50 15/09/2010 D51 15/09/2010 In I3 I have 31/01/10, J3 28/02/10 etc etc running along to AR3 which has 31/12/12 I want to write a formula that simply says to check the dates in column D42 - D51 against the date in I3 and if the date in D42-51 is prior to I3 add 1, So my first forumla for January should = 5, February = 5, March 6 etc tec I have tried a countif forumula but it doesn't seem to work. Any ideas appreciated. Thank you |
#3
|
|||
|
|||
possible countif formula?
Hi Ashish
That's great thank you, works a treat, could I ask what the & is doing? "Ashish Mathur" wrote: Hi, Try this formula and copy to the right =countif($D$42:$D$51,"="&I$3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mark D" wrote in message ... Hi Again everyone I am trying to get a formula in (possible countif) which doesn't seem to be working In column D I have the following dates D42 30/03/2009 D43 01/06/2005 D44 06/02/2009 D45 30/03/2009 D46 19/10/2009 D47 08/03/2010 D48 15/07/2010 D49 15/07/2010 D50 15/09/2010 D51 15/09/2010 In I3 I have 31/01/10, J3 28/02/10 etc etc running along to AR3 which has 31/12/12 I want to write a formula that simply says to check the dates in column D42 - D51 against the date in I3 and if the date in D42-51 is prior to I3 add 1, So my first forumla for January should = 5, February = 5, March 6 etc tec I have tried a countif forumula but it doesn't seem to work. Any ideas appreciated. Thank you |
#4
|
|||
|
|||
possible countif formula?
Hi,
The & symbol combines/aggregates strings. For e.g. if you have AS in cell D5 and DF in cell E5, in F5, enter =D5&E5 - your answer will be ASDF. Since I3 has to be treated as a cell reference, it cannot be within double quotes and therefore the cell reference and = are combined with the & symbol -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mark D" wrote in message ... Hi Ashish That's great thank you, works a treat, could I ask what the & is doing? "Ashish Mathur" wrote: Hi, Try this formula and copy to the right =countif($D$42:$D$51,"="&I$3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mark D" wrote in message ... Hi Again everyone I am trying to get a formula in (possible countif) which doesn't seem to be working In column D I have the following dates D42 30/03/2009 D43 01/06/2005 D44 06/02/2009 D45 30/03/2009 D46 19/10/2009 D47 08/03/2010 D48 15/07/2010 D49 15/07/2010 D50 15/09/2010 D51 15/09/2010 In I3 I have 31/01/10, J3 28/02/10 etc etc running along to AR3 which has 31/12/12 I want to write a formula that simply says to check the dates in column D42 - D51 against the date in I3 and if the date in D42-51 is prior to I3 add 1, So my first forumla for January should = 5, February = 5, March 6 etc tec I have tried a countif forumula but it doesn't seem to work. Any ideas appreciated. Thank you |
Thread Tools | |
Display Modes | |
|
|