Count Number of Cells in a Range

T

TKrepitch

I know the best answer to this question is to use a pivot table, but
it's not my file - I'm just trying to help someone out here. :)

There is a table that lists a category from B6:B26 and the months from
C5:N5. Within B6:B26, there are four different possibilities (say A,
B, C and D).

What we need to do is write an equation that will tell us how many As,
Bs, Cs and Ds there are in each month, but only if the number in the
table is greater than zero.

I guess it would be something like a COUNTIFWHERE function, if that
were to exist. Any ideas?

Sorry if this is confusing...I'd be happy to clarify if I can. :)
 
B

Biff

Hi!
I know the best answer to this question is to use a pivot table
Click to expand...

The amount of time it takes you to create a pivot table for this I could
have done it10 times with a formula! (and the resultant table looks better!)
<g>

I'm assuming the months from C5:N5 are text entries like Jan, Feb, Mar, etc.

I did it a little different. I transposed the row and column headers:


...........O..........P..........Q..........R..........S
5..................Cat1.....Cat2.....Cat3.....Cat4
6......Jan
7......Feb
8......Mar
9......Apr
...
17....Dec

Enter this formula in P6:

=SUMPRODUCT(($B$6:$B$26=P$5)*($C$5:$N$5=$O6)*($C$6:$N$26>0))

Copy across to S6 then down to row 17.

Biff
 
T

TKrepitch

Thanks, Biff! That is very slick. :)
Hi!


The amount of time it takes you to create a pivot table for this I could
have done it10 times with a formula! (and the resultant table looks better!)
<g>

I'm assuming the months from C5:N5 are text entries like Jan, Feb, Mar, etc.

I did it a little different. I transposed the row and column headers:


..........O..........P..........Q..........R..........S
5..................Cat1.....Cat2.....Cat3.....Cat4
6......Jan
7......Feb
8......Mar
9......Apr
..
17....Dec

Enter this formula in P6:

=SUMPRODUCT(($B$6:$B$26=P$5)*($C$5:$N$5=$O6)*($C$6:$N$26>0))

Copy across to S6 then down to row 17.

Biff
Click to expand...
 
Top