countif question

[Jennie]

hi all,
I'd like to make a summary sheet that will count the total # of product
within a certain month so if I type in the month # in one cell the # of total
products will show up. for example

month # product
1 a
1 b
2 a

so if I type in 1 for month, 2 will show up for products.
thanks in advance.

[greg7468]

Hi Jennie,

assuming your months are in column A and your products are in column B

in C1 put this formula

=COUNTIF(A1:A100,D1)

Now put the number of the month you want to count for into D1.

This will only count the amount of times the month appears in column A.

If you need help counting products per month or anything else come back

HTH.

[Ron de Bruin]

Hi Jennie

This will count the dates that are in Jan in B1:B10

=SUMPRODUCT((MONTH(B1:B10)=1)*1)

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jennie" <[email protected]> wrote in message news:[email protected]...
> hi all,
> I'd like to make a summary sheet that will count the total # of product
> within a certain month so if I type in the month # in one cell the # of total
> products will show up. for example
>
> month # product
> 1 a
> 1 b
> 2 a
>
> so if I type in 1 for month, 2 will show up for products.
> thanks in advance.

[Jennie]

I'm looking for a generic formula that will work for any month, not just
January. In place of month I tried to type in a range of cells (say A1:a10)
and instead of "1" I tried to type in the cell where I will enter the month
I'm interested in (say C1), but this gives me a #ref error.
So I tried: =sumproduct((A1:A10(B1:B10)=c1)*c1)
How can I make a generic formula that will work for any month?

"Ron de Bruin" wrote:

> Hi Jennie
>
> This will count the dates that are in Jan in B1:B10
>
> =SUMPRODUCT((MONTH(B1:B10)=1)*1)
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Jennie" <[email protected]> wrote in message news:[email protected]...
> > hi all,
> > I'd like to make a summary sheet that will count the total # of product
> > within a certain month so if I type in the month # in one cell the # of total
> > products will show up. for example
> >
> > month # product
> > 1 a
> > 1 b
> > 2 a
> >
> > so if I type in 1 for month, 2 will show up for products.
> > thanks in advance.
>
>
>

[Ron de Bruin]

=sumproduct((A1:A10(B1:B10)=c1)*c1)

use this

=sumproduct((A1:A10(B1:B10)=C1)*1)
don't use *c1

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jennie" <[email protected]> wrote in message news:[email protected]...
> I'm looking for a generic formula that will work for any month, not just
> January. In place of month I tried to type in a range of cells (say A1:a10)
> and instead of "1" I tried to type in the cell where I will enter the month
> I'm interested in (say C1), but this gives me a #ref error.
> So I tried: =sumproduct((A1:A10(B1:B10)=c1)*c1)
> How can I make a generic formula that will work for any month?
>
> "Ron de Bruin" wrote:
>
>> Hi Jennie
>>
>> This will count the dates that are in Jan in B1:B10
>>
>> =SUMPRODUCT((MONTH(B1:B10)=1)*1)
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Jennie" <[email protected]> wrote in message news:[email protected]...
>> > hi all,
>> > I'd like to make a summary sheet that will count the total # of product
>> > within a certain month so if I type in the month # in one cell the # of total
>> > products will show up. for example
>> >
>> > month # product
>> > 1 a
>> > 1 b
>> > 2 a
>> >
>> > so if I type in 1 for month, 2 will show up for products.
>> > thanks in advance.
>>
>>
>>

[Jennie]

yeah I need help with counting products per month, if you don't mind helping
me with that

"greg7468" wrote:

>
> Hi Jennie,
>
> assuming your months are in column A and your products are in column B
>
> in C1 put this formula
>
> =COUNTIF(A1:A100,D1)
>
> Now put the number of the month you want to count for into D1.
>
> This will only count the amount of times the month appears in column
> A.
>
> If you need help counting products per month or anything else come
> back
>
> HTH.
>
>
> --
> greg7468
>
>
> ------------------------------------------------------------------------
> greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
> View this thread: http://www.excelforum.com/showthread...hreadid=381263
>
>

[Jennie]

sorry to bother you again, I'm still getting a #ref error with the new
formula. is there another one I can use?

"Ron de Bruin" wrote:

> =sumproduct((A1:A10(B1:B10)=c1)*c1)
>
> use this
>
> =sumproduct((A1:A10(B1:B10)=C1)*1)
> don't use *c1
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Jennie" <[email protected]> wrote in message news:[email protected]...
> > I'm looking for a generic formula that will work for any month, not just
> > January. In place of month I tried to type in a range of cells (say A1:a10)
> > and instead of "1" I tried to type in the cell where I will enter the month
> > I'm interested in (say C1), but this gives me a #ref error.
> > So I tried: =sumproduct((A1:A10(B1:B10)=c1)*c1)
> > How can I make a generic formula that will work for any month?
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Jennie
> >>
> >> This will count the dates that are in Jan in B1:B10
> >>
> >> =SUMPRODUCT((MONTH(B1:B10)=1)*1)
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "Jennie" <[email protected]> wrote in message news:[email protected]...
> >> > hi all,
> >> > I'd like to make a summary sheet that will count the total # of product
> >> > within a certain month so if I type in the month # in one cell the # of total
> >> > products will show up. for example
> >> >
> >> > month # product
> >> > 1 a
> >> > 1 b
> >> > 2 a
> >> >
> >> > so if I type in 1 for month, 2 will show up for products.
> >> > thanks in advance.
> >>
> >>
> >>
>
>
>

[Ron de Bruin]

Use the formula I posted and change it to this

=SUMPRODUCT((MONTH(B1:B10)=C1)*1)



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jennie" <[email protected]> wrote in message news:[email protected]...
> sorry to bother you again, I'm still getting a #ref error with the new
> formula. is there another one I can use?
>
> "Ron de Bruin" wrote:
>
>> =sumproduct((A1:A10(B1:B10)=c1)*c1)
>>
>> use this
>>
>> =sumproduct((A1:A10(B1:B10)=C1)*1)
>> don't use *c1
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Jennie" <[email protected]> wrote in message news:[email protected]...
>> > I'm looking for a generic formula that will work for any month, not just
>> > January. In place of month I tried to type in a range of cells (say A1:a10)
>> > and instead of "1" I tried to type in the cell where I will enter the month
>> > I'm interested in (say C1), but this gives me a #ref error.
>> > So I tried: =sumproduct((A1:A10(B1:B10)=c1)*c1)
>> > How can I make a generic formula that will work for any month?
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi Jennie
>> >>
>> >> This will count the dates that are in Jan in B1:B10
>> >>
>> >> =SUMPRODUCT((MONTH(B1:B10)=1)*1)
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >> "Jennie" <[email protected]> wrote in message news:[email protected]...
>> >> > hi all,
>> >> > I'd like to make a summary sheet that will count the total # of product
>> >> > within a certain month so if I type in the month # in one cell the # of total
>> >> > products will show up. for example
>> >> >
>> >> > month # product
>> >> > 1 a
>> >> > 1 b
>> >> > 2 a
>> >> >
>> >> > so if I type in 1 for month, 2 will show up for products.
>> >> > thanks in advance.
>> >>
>> >>
>> >>
>>
>>
>>