# DATE AND IF HEELLLLLLPPP :s

#### pjbeh

##### New Member
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...

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Try:

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

Probably:

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

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

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

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?

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

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

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!

Replies
5
Views
243
Replies
15
Views
531
Replies
8
Views
404
Replies
19
Views
438
Replies
2
Views
396

1,217,681
Messages
6,137,952
Members
450,100
Latest member
mscetr

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

### Which adblocker are you using?

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

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