'If' conditions on multiple criteria and rows

[VBAStarter]

I tried different possibilities to combine the following conditions:
condition 1: state (column D) 'vrijgegeven' has to be true
condition 2: 'vrijgegeven' has to be done between 1-1-2015 en 31-12-2015 (column C)
condition 3: If number in column A appears more than one time then condition 4 has to be activated
condition 4: if date in 'selectie start' (column B) is between now and 1-12-2015 and the number in column A appears more then one time the answer in column I is: 'reminder' otherwise: 'geen reminder'

At this moment I can only get a formule that gives me an answer in the corresponding row without looking to the rows from the same number (column A). So condition 4 is my biggest problem.

Can anyone help me with this case?

[Pete_UK]

I'm not sure that you have covered all conditions in your description, but try this in I2:

=IF(AND(D2="vrijgegeven",YEAR(C2)=2015,COUNTIF(A:A,A2)>1),"reminder","geen reminder")

Note that you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings, then you can copy the formula down as required.

Hope this helps.

Pete

[VBAStarter]

Pete, thanks a lot for your help so far.
Indeed the (,) should be (; ) in my Excel. The solution you suggested gives reminders and non-reminders for the same business (column A).
I hoped to manage it in a way that if in column B for one business (column A) there is a data after 1-12-2015, I dont get an answer 'reminder' in I. '
Now you see business (see attachment 205 (in red)) has two 'reminders' and one 'geen reminder'.
In this case i would like to have 'geen reminder' for all business rows 205 in column I because there is a data younger (B9) than 1-12-2015 so this business don't need a reminder.
is this description clear to you?

[JeteMc]

Assuming that 1-12-2015 is December 1st 2015 and not January 12th 2015 then the following may work (paste into cell I2 and drag the fill handle down):

Please Login or Register  to view this content.

If the above formula does not yield the desired results then it would be helpful to manually place the desired results so that we will have something with which to compare the results of our proposed formulas/code.
Let us know if you have any questions.

[VBAStarter]

JeteMc,
Thanks for your support!
As you can see in the Excel-attachment (version 1.3) it works for the company's that appear more than ones in column A (in red), but it gives a wrong answer (orange in row 211). This is in my opinion due to the condition IF(AND(OR(D2="vrijgegeven";AND(COUNTIFS(A$2:A$18;A2)>1;COUNTIFS(A$2:A$18;A2;D$2:D$18;"vrijgegeven"))) because this will lead to twice 'FALSE', and therefore the result 'reminder'.

So I think we're on the good road, but still not where we supposed to be.
Still new suggestions?

[AliGW]

Does this help?

=IF(D2<>"vrijgegeven","geen reminder",IF(AND(OR($D2="vrijgegeven",AND(COUNTIFS($A$2:$A$18,$A2)>1,COUNTIFS($A$2:$A$18,$A2,$D$2:$D$18,"vrijgegeven"))),COUNTIFS($A$2:$A$18,$A2,$B$2:$B$18,">"&DATE(2015,12,1))),"geen reminder","reminder"))

[VBAStarter]

Does this help?

=IF(D2<>"vrijgegeven","geen reminder",IF(AND(OR($D2="vrijgegeven",AND(COUNTIFS($A$2:$A$18,$A2)>1,COUNTIFS($A$2:$A$18,$A2,$D$2:$D$18,"vrijgegeven"))),COUNTIFS($A$2:$A$18,$A2,$B$2:$B$18,">"&DATE(2015,12,1))),"geen reminder","reminder"))

AliGW,

Thanks! This will help me a lot!
The only thing that still has to be done, is that the condition for column C has to be between 1-1-2015 en 31-12-2015. (I wrote this as: =IF(AND($C2<(TODAY()-1813);$C2>(TODAY()-2177);ISNUMBER($C2));"TRUE";"-") because over e.g. three month other company's will receive a reminder.

I hope you can fix this in the formula!

[JeteMc]

Try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[bebo021999]

Alternative formula:

Please Login or Register  to view this content.

I interpret in brief way , 3 criterias to returns "reminder":

D2="vrijgegeven"

YEAR(C2)=2015

COUNTIF($A$2:$A$18,A2)=COUNTIFS($A$2:$A$18,A2,$B$2:$B$18,"<"&DATE(2015,12,1))
(Count of specific company = count of that company with date <1/12/2015)

[VBAStarter]

JeteMc & bebo021999,

thanks for your help. It works!
Now I'm aible to check more then 6000 cases in one time!
Both solutions do the job well.

I appreciate your professional help for an Excel-amateur like me.
(of course also thanks to Pete-UK en AliGW for their help earlier on)

VBAStarter

[AliGW]

Pleasure. Thanks for the rep.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.