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...
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top