2

I was able to count how many occurrences of dates between 2 date ranges, however now I having difficulty counting how many times Yes occurs between 2 date ranges.

this formula works for counting how many times a date range occurs where I23 and I24 are the dates in the range (11/30 - 12/15)

=COUNTIFS(non_activated_accounts_100112!J2:K4000, ">="&$I$23,non_activated_accounts_100112!$J$2:$K$4000,"<="&$I$24)

This does a great job of counting how many instances of dates showing between the two dates. however I would like to add an additional check column J:2:4000 for "yes", and get result of how many instances of "yes" are there between the 2 dates.

terdon
  • 54,564
tcole
  • 23

1 Answers1

1

Normally better to stick to COUNTIFS, it's more efficient, - you can add more conditions, i.e......

`=COUNTIFS('Load Entry'!$N$4:$N$563,"Yes",'Load Entry'!$N$4:$N$563,">="&$A$28,'Load Entry'!$N$4:$N$563,"<="&$A$27)'

......But if you have 2 columns of dates and only one "yes" column then COUNTIFS won't work because all the ranges need to be the same size, so back to SUMPRODUCT.....

Can you count any row twice (if both dates in that row are within the date range and "Yes" appears in that row)? If so try this version

=SUMPRODUCT((non_activated_accounts_100112!J2:K4000>=$I$23)*(non_activated_accoun‌​ts_100112!$J$2:$K$4000<=$I$24)*(non_activated_accoun‌​ts_100112!$N$2:$N$4000="yes"))

....or if each row should only be counted once at most.....

=SUMPRODUCT(((non_activated_accounts_100112!J2:J4000>=$I$23)*(non_activated_accoun‌​ts_100112!$J$2:$J$4000<=$I$24)+(non_activated_accounts_100112!K2:K4000>=$I$23)*(non_activated_accoun‌​ts_100112!$K$2:$K$4000<=$I$24)>0)*(non_activated_accoun‌​ts_100112!$N$2:$N$4000="yes"))

That second one splits out columns J and K .....

barry houdini
  • 11,212