I have data in two columns. Column B has calculated string values of "BMAJOR" and "BMINOR" and column C has date values.
I am trying to get a count of how many occurences of "BMAJOR" or "BMINOR" occur for every week starting today. I have column AJ which returns today() and column AK which returns today()+7. I can individually count number of occurences of dates that fall within these dates with the following formula. This for row 2.
=COUNTIF('Decision Tree'!$C$4:$C$215,"<="&AK2)-COUNTIF('Decision Tree'!$C$4:$C$215,"<"&AJ2)
This counts number of dates between two dates computed in columns AJ(today()) & AK(today()+7). I am now trying to count only those dates that fall within the week, which have "BMAJOR" in the corresponding column in the same row.
I tried using an AND statement:
=(COUNTIF(('Decision Tree'!$C$4:$C$215,"<="&AK2)*('Decision Tree'!$B$4:$B$215,"=BMAJOR")))-(COUNTIF(('Decision Tree'!$C$4:$C$215,"<="&AJ2)*('Decision Tree'!$B$4:$B$215,"=BMAJOR")))
I also tried entering it as an array and naming the ranges. But it returns an error every time. Any help will be highly appreciated. Below is a sample data for next week:
So basically for week 17/09/2007 to 24/09/2007 the formula should return values of 4 (BMAJOR) and 4 (BMINOR).
I would be grateful for any help or ideas.
I am trying to get a count of how many occurences of "BMAJOR" or "BMINOR" occur for every week starting today. I have column AJ which returns today() and column AK which returns today()+7. I can individually count number of occurences of dates that fall within these dates with the following formula. This for row 2.
=COUNTIF('Decision Tree'!$C$4:$C$215,"<="&AK2)-COUNTIF('Decision Tree'!$C$4:$C$215,"<"&AJ2)
This counts number of dates between two dates computed in columns AJ(today()) & AK(today()+7). I am now trying to count only those dates that fall within the week, which have "BMAJOR" in the corresponding column in the same row.
I tried using an AND statement:
=(COUNTIF(('Decision Tree'!$C$4:$C$215,"<="&AK2)*('Decision Tree'!$B$4:$B$215,"=BMAJOR")))-(COUNTIF(('Decision Tree'!$C$4:$C$215,"<="&AJ2)*('Decision Tree'!$B$4:$B$215,"=BMAJOR")))
I also tried entering it as an array and naming the ranges. But it returns an error every time. Any help will be highly appreciated. Below is a sample data for next week:
So basically for week 17/09/2007 to 24/09/2007 the formula should return values of 4 (BMAJOR) and 4 (BMINOR).
I would be grateful for any help or ideas.