Thread: IF Formula not giving me what I need

Hi:
This is the first time I've posted in here because I thought I knew everything (LOL)..apparently, I don't! I hope I can explain the issue I am having without confusing you all too much.. Here we go

In my report I have sort on to sort by account number:
Column D: Customer Account Number
Column E: Work Completion Date

I need to find out how many times the account number in Column D appears within 30 days of the first date it appears.

So the formulas I have so far are:
Column F: =E2 (giving me the date in number form)
Column G: =IF((D2=D1),IF(((F2-F1)<31),(F2-F1),"NA"),"FIRST") **This is supposed to tell me if the account comes up "FIRST", hence the reason I have the report sorted by account #.
Column H: =IF(G2="FIRST",1,IF(G2="NA",1,H1+1)). **This column gives me the amount of times the account appears less based on the formula in Column G.

Now the issue I am having is that if an account shows up on Sept 1st, Sept 3rd, Sept 20th and October 5th. I want a column to tell me that I visited this account 3 times within 30 days of Sept 1st. But I need it also to tell me that in October, we were there 2 times within 30 days because we were there on Sept 20th and October 5th. I basically need it to record the amount of times we were out to each account within 30 days of the first visit, (which is what I currently have working), however I need the counter to start over each time we exceed a 30 day mark and start another..

PS - I'm having trouble uploading the file for an example

I'm sure I've confused some of the best on here and I apologize. I don't even know if what I'm asking for is even possible??!! Please help if you can..I'll answer any questions you may have!

Hi Cable 1208,

Welcome to the forum.
You have explained in detail but a sample file will definitely add into it, please upload. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

Hi Cable,

Firstly, would request you to work on the suggestion provided by NBVC.
After this, would suggest you to delete the extra sheet tabs from your workbook, if any and post just the one sheet tab with relevant sample data in it. Also try zipping it before uploading.

Regards,
DILIPandey

<click on below 'star' if this helps>