countif question

[Guest]

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]

Hi Jennie

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

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

 

[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.

 

[Guest]

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]

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

use this

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

 

[Guest]

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

 

[Guest]

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]

Use the formula I posted and change it to this

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