How to use Countif with range and specific filter

**dystop1a** · 01-09-2013, 05:21 PM

Hi -

Can you please help me, I'm suppose to create a formula that counts the number of row in Column B ranges from 5 - 10 for Column A that is marked 'Pending'.

Here's the table:

1 Column A Column B
2 Pending 10
3 Pending 3
4 Overdue 2
5 Pending 5
6 Overdue 2
7 Overdue 2
8 Overdue 2
9 Completed 5
10 Pending 1
11 Overdue 0
12 Pending 1
13 Pending 0
14 Pending 0

In this example, I should be getting 2.

Thanks in advance.

**FDibbins** · 01-09-2013, 05:22 PM

sounds like homework?

**djapigo** · 01-09-2013, 05:27 PM

Try this...

=SUM((B2:B14>=5)*(B2:B14<=10)*(A2:A14="Pending"))

Since this is an array formula, you must use array enter (CTRL-SHIFT-ENTER) instead of just ENTER... (you'll see the magical curly brackets around your formula if you did it right)

Let me know if this works for you...

Dennis

Mabuhay!

Note: since you only have the Excel 2003, this is one way to do this... SUMPRODUCT might work too... the newer versions, SUMIFS are the new formulas...

**oeldere** · 01-09-2013, 05:30 PM

@Fdibbins

If so, i just point in the right direction.

Or use sumproduct or sumif or pivot table for that.

You can also use filter in combination with subtotal.

**dystop1a** · 01-09-2013, 06:56 PM

Exactly what I want... Mabuhay.

Thanks all!

Originally Posted by djapigo

Try this...

=SUM((B2:B14>=5)*(B2:B14<=10)*(A2:A14="Pending"))

Since this is an array formula, you must use array enter (CTRL-SHIFT-ENTER) instead of just ENTER... (you'll see the magical curly brackets around your formula if you did it right)

Let me know if this works for you...

Dennis

Mabuhay!

Note: since you only have the Excel 2003, this is one way to do this... SUMPRODUCT might work too... the newer versions, SUMIFS are the new formulas...