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
  • 52,568
  • 14
  • 124
  • 170
tcole
  • 23
  • 3
  • I have tried =SUMPRODUCT(('Load Entry'!$N$4:$N$563="Yes")*('Load Entry'!$N$4:$N$563>=$A$28)*('Load Entry'!$N$4:$N$563>=$A$27)) – tcole Dec 03 '12 at 18:52
  • I posted an answer but I see you have different ranges - in your first COUNTIFS you are looking at 2 columns for dates (J and K) - are there dates in both? COUNTIFS requires all ranges to be the same size so you can't have date ranges as two columns and "Yes" range as one - in that case SUMPRODUCT might be better....but can you have a single row where J and K both have a date in your range, if so do you want to count it twice? – barry houdini Dec 03 '12 at 19:01
  • Yes dates are in both - 1st call column J, last call or attempt column K, both need to be looked at and if column N = "Yes" count, – tcole Dec 03 '12 at 19:19
  • =COUNTIFS(non_activated_accounts_100112!N2:N4000,Yes,non_activated_accounts_1001‌​12!J2:K4000,">="&$I$23,non_activated_accounts_100112!$J$2:$K$4000,"<="&$I$24) Gives me a #Value error – not sure where the problem is – tcole Dec 03 '12 at 19:56

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
  • 10,892
  • 1
  • 20
  • 25
  • I'm using the following =COUNTIFS(non_activated_accounts_100112!J2:K4000,">="&$I$23,non_activated_accounts_100112!$J$2:$K$4000,"<="&$I$24)&non_activated_accounts_100112!N2:N3000="Yes" and it return a "false" as result rather than 5 – tcole Dec 03 '12 at 19:23
  • =COUNTIFS(non_activated_accounts_100112!N2:N4000,Yes,non_activated_accounts_100112!J2:K4000,">="&$I$23,non_activated_accounts_100112!$J$2:$K$4000,"<="&$I$24) Gives a #Value error – tcole Dec 03 '12 at 19:35
  • For differently sized ranges COUNTIFS doesn't work as per my comment above - I edited my answer for SUMPRODUCT solution – barry houdini Dec 03 '12 at 19:56
  • I get #REF! when trying to use Sumproduct - =SUMPRODUCT((non_activated_accounts_100112!$J$2:$J$4000=I23)*(non_activated_accoun‌​ts_100112!$J$2:$J$4000=I24)*(non_activated_accoun‌​ts_100112!$N$2:$N$4000="yes")) – tcole Dec 03 '12 at 20:36
  • I think something went wrong with the sheet name - when I pasted from here I got the same error, there's no problem with the formula, in essence, might be some odd characters when you copy from here - please try re-typing the sheet name, or to simplify things just put the formula on the sheet with the data then you can use a shorter version like `=SUMPRODUCT((J2:K4000>=$I$23)*($J$2:$K$4000<=$I$24)*($N$2:$N$4000="yes"))` for testing purposes at least that might be simpler..... – barry houdini Dec 03 '12 at 20:55