1

I have the following columns in my excel spreadsheet:

Participant ID; Date of Event; 3 or more events in one month

I want to auto populate the 3rd column 3 or more events in one month - by looking at the first two columns. If there are 3 of the same Participant IDs in the first column and 3 different event dates (within a one month time period - so 30 days) in the second column - the third column should auto populate to say Yes; otherwise No.

Any assistance anyone can give would be greatly appreciated!

1

Blackwood's answer calculates whether a participant had 3 or more events in a particular month.

But I think the OP wants to know whether they had 3 or more events in a 30 day span. This formula does that using COUNTIFS():

=IF(COUNTIFS(A$2:A$32,A2,B$2:B$32,">="&(B2-15),B$2:B$32,"<="&(B2+15))>2,"Yes","No")

enter image description here

  • Did COUNTIFS exist in Excel 2007? – Blackwood Dec 11 '17 at 18:14
  • 1
    Yes, I checked. – Bandersnatch Dec 11 '17 at 18:17
  • Sorry, my mistake, I thought it was added in Excel 2010. – Blackwood Dec 11 '17 at 18:18
  • No problem, a lot of stuff was added after XL 2007. Glad to help. I'm still not sure whether she wants a particular month or a 30 day span, tho. :-) – Bandersnatch Dec 11 '17 at 18:21
0

[Edit] I apologise, I hadn't noticed that the question is tagged Excel 2007, the answer I initially gave will not work with older versions of Excel. The following updated answer should work.

You can easily do this by adding a helper column (which you can hide if you don't want it visible).

Assuming Participant ID is in Column A and Date of Event is in Column B, and that the headers are in Row 1 with the data starting in Row 2, insert a new Column C (which you can hide if you want).

In cell C2 enter the following formula and copy it down all the rows of data.

A2&"/"&TEXT(B2,"mmm-yy")

This will return the participant ID followed by the month and year of the event.

Now enter the following formula in cell D2 and copy it down all the rows of data.

=IF(COUNTIF($C:$C,$C2)>=3,"Participant "&$A2&" has three or more events in "&TEXT($B2,"mmm-yy"),"")

This counts the number of rows where the Participant ID and month are the same as in this row (in other words the number of events this participant has in this month); that number is three or more, a note is displayed.

enter image description here

  • Thank you - this does work. But - because I am setting this up as data entry template (I should have stated this in my original question) - once I copy the formula's down - it's filling columns C and D. Both columns change appropriately once data is entered into A and B - and while I can hide column C - I'm concerned that Column D will confuse data entry folks. Any other suggestions to fix this? – MaryAnn Dec 11 '17 at 18:09
  • @MaryAnn it depends on what you want to do. You could wait until data entry is complete before adding columns C and D. Or you could hide both columns C and D, then unhide column D once data entry is complete. – Blackwood Dec 11 '17 at 18:13
  • You don't need the extra column, see the other answer. – Bandersnatch Dec 11 '17 at 18:20
  • Thank you all so much for your help - I've been checking on whether the period is for 30 days or one month, unfortunately - I haven't heard back yet - I should know by tomorrow morning. I'm assuming it's 3 services within 30 days from the first date of service. But will confirm in the morning. – MaryAnn Dec 11 '17 at 20:30
  • My mistake, it is based on the calendar month. Thank you both for all your help! I'd been playing with vlookups and matches and was starting to look into how to hard code this. – MaryAnn Dec 12 '17 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.