COUNTIF only when two criteria met

March 5, 2009 at 11:50:15
Specs: Windows XP
I need to count the time that "XX" appears between F5:F1000 but ONLY when A5:A1000 equals a given date. I need to count all occurances of "XX" in the "F" column by I need them separated by date. I can use different formulas for each weekday. Any help, greatly appreciated.

See More: COUNTIF only when two criteria met

Report •

March 5, 2009 at 12:32:01
I believe you are looking for the following array formula.

{=SUM((A5:A1000="criteria 1")*(F5:F1000="criteria 2"))}

Enter this formula using Ctrl-Shift-Enter to place the {} brackets around it. Don't type the brackets, that won't work. use Ctrl-Shift-Enter and they will magically appear.

Report •

March 5, 2009 at 12:39:37
Excel readily accepted the formula and I did do the "CNTL+SHFT+ENTER" and it inserted the "{}" brackets, however even though I know I have data that meets the criteria, it's still returning a value of zero ? Any ideas ?

Report •

March 5, 2009 at 12:44:36

I replaced the SUM with COUNT. Now I get a response of 996 instead of 0.

Problem is that I'm trying to count the instances of "XX" in column "F" only when the column "A" value of the same row also equals the date of "3/2/2009". The answer should be 17. I think that answer that I'm seeing is counting every single X on the entire sheet.

Hope this extra information helps and thanks in advance for your assistance.

Report •

Related Solutions

March 5, 2009 at 13:08:57
Unfortunately, I can't see your spreadsheet from here, so it's kind of hard to tell me why it's not working.

The theory behind an SUM Array formula is that it checks each cell in one array against it's corresponding cell in the other array. i.e. A5 against F5, A6 against F6, etc. If the condition for A5 is TRUE, it equals 1, if the condition for F5 is TRUE, therefore 1 * 1 is 1. If either condition is FALSE, 1 * 0 = 0, or 0 * 1 = 0.

After it has evaluated all of the array pairs, it SUMS all the 1's resulting in the number of array pairs that were both TRUE.

Here's what I did as a test:

  A           F
1 a          xx
2 b          xx
3 c          xx
4 a          xx
5 b          xx
6 c          xx


This formula returns 2, as expected, since A1,F1 and A4,F4 are TRUE.

The best I can offer at this point is for you to ensure that the criteria in your formula matches exactly what is in the cells.

Report •

March 5, 2009 at 13:11:56
I just re-read your last post. It may be an issue in how your dates are in the cell vs. the date string used in your formula.

If Excel is interpreting them differently, then the array formula won't find that date.

Please post your formula.

Report •

March 5, 2009 at 13:21:44
You hit the nail on the head. It was related to the date format. Since this is a recurring weekly count, I can work it differently. I used M, T, W, TH and F instead of the actual dates and it's counting perfectly. I'll just hide that column and display the week's start date at the top of the worksheet. It'll work perfectly. Thanks a million for your assistance.

Report •

March 5, 2009 at 14:17:21
Glad to have been of assistance.

Report •

Ask Question