COUNTIF once only if value in row

[surkdidat]

In columns AL and AM a date can be inserted, and in Column AN states "Accepted" or "Rejected"

What I need is a formula that counts the numbers of Accepted, but then refers to the dates in columns AL or AM

(Providing AN is accepted) - If a date is in AM I need it to ignore the date in Column AL and count the number of dates in column AM.

(Providing AN is accepted) - If there is a date in column AL but not in column AM, I want it to count the date in that column

So for instance

----AL----|---AM----|----AN----|
12/04/18|-----------|Accepted |...……… (Count 12 April 2018)

12/04/18|15/04/18|Accepted |...……… (Count 15 April 2018 only)
12/04/18|------------|----------|...……… (Do not count anything)
12/04/18|15/04/18|----------|...………

(Do not count anything)

There will never be a date in column AM before the date in column AL
There will never be a date only in AM. If there is a date in AM there will always be a date in AL
​

​

_{^{<strike>
</strike>}}​

 

[Aladin Akyurek]

Looks like... Control+shift+enter, not just enter:

=SUM(IF(AN2:AN10="accepted",IF(ISNUMBER(AM2:AM10)+ISNUMBER(AL2:AL10),1)))

 

[surkdidat]

Apologies Aladin, I forgot to add in Column B I have the dates, such as

b25 - 09/04/18
B26 - 16/04/18
B27 - 23/04/18
etc

The formula will go in cell C25, c26, c27 etc, and I need the COUNTIF/SUMIF to count the occurances between the dates, so in cell C25 it will use the formula for all occurances between 9/4/18 and 15/04/18

Looks like... Control+shift+enter, not just enter:

=SUM(IF(AN2:AN10="accepted",IF(ISNUMBER(AM2:AM10)+ISNUMBER(AL2:AL10),1)))

 

[Aladin Akyurek]

I don't follow. What is the relation between this B25:B and AM, AL, and AN columns?

 

[surkdidat]

In B25 I have a date of 09 April 2018 (for example). In B26 I have a date of 16 April 2016. What I need in C25, C26 etc is to count the number of occurances in my first post of that situation. So, whilst the formula above counts every line it has "Approved" and a date, I need it to count the number of occurances between the dates.
So, in the below example

It would count
2 between 09 April-15 April (12 and 14 April)
0 between 16 April-22 April
1 between 23 April-30 April

So for instance

----AL----|---AM----|----AN----|
12/04/18|-----------|Accepted |...……… (Count 12 April 2018)
14/04/18|-----------|Accepted |...……… (Count 14 April 2018)

12/04/18|25/04/18|Accepted |...……… (Count 25 April 2018 only)
12/04/18|------------|----------|...……… (Do not count anything)
12/04/18|20/04/18|----------|...…… (Do not count anything)

I don't follow. What is the relation between this B25:B and AM, AL, and AN columns?

​