# COUNTIF based on two criteria--Help

##### New Member
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.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### nachiketdp

##### Board Regular
Make a column for concatenate of bmajor/bminor and the date. now put the criteria as the concatenated value of bmajor/bminor and the date.

HTH,
Regards,
Nachiket Pendharkar

#### barry houdini

##### MrExcel MVP
Assuming you want to count for just seven days, i.e. today and the next 6 days

=SUMPRODUCT(--('Decision Tree'!\$C\$4:\$C\$215< AK2),--('Decision Tree'!\$C\$4:\$C\$215 >=AJ2),--('Decision Tree'!\$B\$4:\$B\$215="BMINOR"))

##### New Member

I tried this one:

=SUMPRODUCT(--('Decision Tree'!\$C\$4:\$C\$215< AK2),--('Decision Tree'!\$C\$4:\$C\$215 >=AJ2),--('Decision Tree'!\$B\$4:\$B\$215="BMINOR"))

But I only get #NA in the computed column. No idea why..

I can concatenate the values, but what criteria? Based on text, right/left formulas?

#### barry houdini

##### MrExcel MVP
But I only get #NA in the computed column. No idea why..
This probably means you have #N/A values somewhere in the data in 'Decision Tree'!\$B\$4:\$C\$215. If you can eliminate the error values the formula should work

##### New Member
This probably means you have #N/A values somewhere in the data in 'Decision Tree'!\$B\$4:\$C\$215. If you can eliminate the error values the formula should work
Yes, I have #NA values in some of the rows.

But these are by design and the #NA is meant to be there. Is there a way I can account for these?

So I was mistaken when asking for help. The column can contain 3 values, viz., BMAJOR, BMINOR & #NA. Apologies.

##### MrExcel MVP
This probably means you have #N/A values somewhere in the data in 'Decision Tree'!\$B\$4:\$C\$215. If you can eliminate the error values the formula should work
Yes, I have #NA values in some of the rows.

But these are by design and the #NA is meant to be there. Is there a way I can account for these?

So I was mistaken when asking for help. The column can contain 3 values, viz., BMAJOR, BMINOR & #NA. Apologies.
Code:
``````=SUMPRODUCT(
--('Decision Tree'!\$C\$4:\$C\$215 >= AJ2),
--('Decision Tree'!\$C\$4:\$C\$215 < AK2),
--ISNUMBER(MATCH('Decision Tree'!\$B\$4:\$B\$215,{"BMINOR"},0)))``````

##### New Member
Code:
``````=SUMPRODUCT(
--('Decision Tree'!\$C\$4:\$C\$215 >= AJ2),
--('Decision Tree'!\$C\$4:\$C\$215 < AK2),
--ISNUMBER(MATCH('Decision Tree'!\$B\$4:\$B\$215,{"BMINOR"},0)))``````
Thanks for the code and all the help. I really appreciate it.

But I am still getting #NA in the computed cell. Maybe it will help if I post a sample of the data showing columns/rows containing the #NA cells. I am sure this is because, where column B contains #NA, column C will also inevitably contain #NA as shown below. I can do some medium level excel stuff, but such code is beyond me. As such, even with the code presented in a platter, I have no idea what it is doing and need to study this a bit more. I will be grateful for any further help.

##### MrExcel MVP
Control+shift+enter...

Code:
``````=SUM(
IF(ISNUMBER('Decision Tree'!\$C\$4:\$C\$215),
IF('Decision Tree'!\$C\$4:\$C\$215 >= AJ2,
IF('Decision Tree'!\$C\$4:\$C\$215 < AK2,
IF(ISNUMBER(MATCH('Decision Tree'!\$B\$4:\$B\$215,{"BMINOR"},0)),
1)))))``````

##### New Member
Brilliant stuff!!! Thanks ever so much for all who helped.