DATE AND IF HEELLLLLLPPP :s

[pjbeh]

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)

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 Pleeeeaaassseee help if you know how! Thankkksssss in advance...

 

[Andrew Poulsom]

Try:

=SUMPRODUCT(--(MONTH(B17:B200)=4),--(N17:N200="a"))

 

[NBVC]

Probably:

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


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

 

[hayden]

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]

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)

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 Pleeeeaaassseee help if you know how! Thankkksssss in advance...

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

 

[pjbeh]

**** you guys are quick! thanks i will try it out tomorrow when i'm at work and let you know how i get on! thanksssss again

 

[pjbeh]

ps barry sorry i forgot to answer your question! i wasn't blankin you i think they are dates yeah ??

 

[pjbeh]

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!