DATE AND IF HEELLLLLLPPP :s

pjbeh

New Member
Joined
Sep 19, 2006
Messages
4
Hi I seem to have a problem and I've spent the past couple of days trying to figure it out!!! (I thought my ALevel in IT might have come in use but obviously not)! LOL! It's not an easy one (unless of course you're an Excel geek) :LOL:

Ok heres the problem:

Column B contains dates in the format of "DD-MMM-YY"

Column N contains ticks if the case is true: (the formula to generate: =IF(AND(B17 >= $G$5, B17 <= $G$6),"a","") - this generates the ticks i think usin a different font.

I need a formula which will: if the month between B17 to B200 = "Apr" (for example), then count the number of ticks in column N.

See I told you it wasn't straightforward :biggrin: Pleeeeaaassseee help if you know how! Thankkksssss in advance...
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Probably:

=Sumproduct(--(Month(B17:B200)=4),--(N17:N200="a"))


Edit: Andrew.....looks like we're following each other today :wink:
 

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
Do you want to count all the ticks in coulmn n or just the ones that are for the months of april?

All the ticks

If(month(b17)=4,countif(Range of all dates,value to count i.e the tick),value if the month isn't april)


The other one I am not positive about.

Hayden
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Hi I seem to have a problem and I've spent the past couple of days trying to figure it out!!! (I thought my ALevel in IT might have come in use but obviously not)! LOL! It's not an easy one (unless of course you're an Excel geek) :LOL:

Ok heres the problem:

Column B contains dates in the format of "DD-MMM-YY"

Column N contains ticks if the case is true: (the formula to generate: =IF(AND(B17 >= $G$5, B17 <= $G$6),"a","") - this generates the ticks i think usin a different font.

I need a formula which will: if the month between B17 to B200 = "Apr" (for example), then count the number of ticks in column N.

See I told you it wasn't straightforward :biggrin: Pleeeeaaassseee help if you know how! Thankkksssss in advance...

I presume you have dates in G5 and G6. What might they be?
 

pjbeh

New Member
Joined
Sep 19, 2006
Messages
4
**** you guys are quick! :biggrin: thanks i will try it out tomorrow when i'm at work and let you know how i get on! thanksssss again
 

pjbeh

New Member
Joined
Sep 19, 2006
Messages
4
ps barry sorry i forgot to answer your question! i wasn't blankin you :oops: i think they are dates yeah ??
 

pjbeh

New Member
Joined
Sep 19, 2006
Messages
4
hey guys it workeedd woohoo! cheers! and i managed to figure out how to sum up the number of 1's, 2's, 3's, etc within a column using the date too! (ok my attempt at makin myself sound extremely intelligent - when in reality you guys did all the hard work!!!) :wink: thanks very much, no doubt i will be posting somethin again soon! :biggrin:
 

Forum statistics

Threads
1,141,222
Messages
5,705,109
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top