# Thread: COUNTIF based on two criteria--Help Thanks: 0 Likes: 0

1. ## COUNTIF based on two criteria--Help

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.

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

3. 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"))

4. Many thanks for your responses.

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?

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

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

7. 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)))```

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

9. 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)))))```

10. Brilliant stuff!!!

Thanks ever so much for all who helped.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•