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.

Hi!

>I know the best answer to this question is to use a pivot table

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>

Thanks, Biff! That is very slick.

Biff wrote:
> Hi!
>
> >I know the best answer to this question is to use a pivot table
>
> 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
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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.
> >

You're welcome. Thanks for the feedback!

Biff

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks, Biff! That is very slick.
>
> Biff wrote:
>> Hi!
>>
>> >I know the best answer to this question is to use a pivot table
>>
>> 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
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >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.
>> >
>