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

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

1,078,491
Messages
5,340,683
Members
399,389
Latest member
JayNExcel

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...