Need a formula.

Column 1 (type) Column 2 (wk)
a 77
b 77
c 78
d 80
e 80
blank 81

I would like the formula to come back for every week and tell me the number
of types that are in that week. Another words in this case there are qty 2
types in wk 77. In week 81 there are 0. In wk 78 there is 1 and week 80
there is 2.

Can anybody help?

Thanks.

Assume that you are having the Values in Column A & B Like the below:-

Col A Col B
Row1 a 77
Row2 b 77
Row3 c 78
Row4 d 80
Row5 e 80
Row6 81

In C1 cell paste the below formula
=SUMPRODUCT(($B$1:$B$100=$B1)*($A$1:$A$100<""))

Copy the C1 cell and paste it to the remaining cells of C Column.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Penny" wrote:

Need a formula.

Column 1 (type) Column 2 (wk)
a 77
b 77
c 78
d 80
e 80
blank 81

I would like the formula to come back for every week and tell me the number
of types that are in that week. Another words in this case there are qty 2
types in wk 77. In week 81 there are 0. In wk 78 there is 1 and week 80
there is 2.

Can anybody help?

Thanks.

Very cool. Now I have two additional variables to add to the equation.
First I only want 1 total number per week in column C. In this formula I am
getting C1 and C2 with the Total count of 2 in it. I only want it to be in
C1. Second is there a way I can paste new data into my columns and not have
to redo the formula. Example would be next week when I past data I might
have 4 types in column 1 for wk 77 instead of 2.

"Ms-Exl-Learner" wrote:

Assume that you are having the Values in Column A & B Like the below:-

Col A Col B
Row1 a 77
Row2 b 77
Row3 c 78
Row4 d 80
Row5 e 80
Row6 81

In C1 cell paste the below formula
=SUMPRODUCT(($B$1:$B$100=$B1)*($A$1:$A$100<""))

Copy the C1 cell and paste it to the remaining cells of C Column.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Penny" wrote:

Need a formula.

Column 1 (type) Column 2 (wk)
a 77
b 77
c 78
d 80
e 80
blank 81

I would like the formula to come back for every week and tell me the number
of types that are in that week. Another words in this case there are qty 2
types in wk 77. In week 81 there are 0. In wk 78 there is 1 and week 80
there is 2.

Can anybody help?

Thanks.

Hi Penny

Set up a new table (say on sheet 2) with just a list of week numbers in
column A starting at A2
Then in B2 enter
=IF(A2="","",COUNTIF(Sheet1!B:B,A2))
Copy down column B on Sheet2 as far as you wish.


--
Regards
Roger Govier

"Penny" wrote in message
...
Very cool. Now I have two additional variables to add to the equation.
First I only want 1 total number per week in column C. In this formula I
am
getting C1 and C2 with the Total count of 2 in it. I only want it to be
in
C1. Second is there a way I can paste new data into my columns and not
have
to redo the formula. Example would be next week when I past data I might
have 4 types in column 1 for wk 77 instead of 2.

"Ms-Exl-Learner" wrote:

Assume that you are having the Values in Column A & B Like the below:-

Col A Col B
Row1 a 77
Row2 b 77
Row3 c 78
Row4 d 80
Row5 e 80
Row6 81

In C1 cell paste the below formula
=SUMPRODUCT(($B$1:$B$100=$B1)*($A$1:$A$100<""))

Copy the C1 cell and paste it to the remaining cells of C Column.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Penny" wrote:

Need a formula.

Column 1 (type) Column 2 (wk)
a 77
b 77
c 78
d 80
e 80
blank 81

I would like the formula to come back for every week and tell me the
number
of types that are in that week. Another words in this case there are
qty 2
types in wk 77. In week 81 there are 0. In wk 78 there is 1 and week
80
there is 2.

Can anybody help?

Thanks.

__________ Information from ESET Smart Security, version of virus
signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Great. This works perfect with one exception. When I have a blank field and
it is not giving me 0 or blank.

"Roger Govier" wrote:

Hi Penny

Set up a new table (say on sheet 2) with just a list of week numbers in
column A starting at A2
Then in B2 enter
=IF(A2="","",COUNTIF(Sheet1!B:B,A2))
Copy down column B on Sheet2 as far as you wish.


--
Regards
Roger Govier

"Penny" wrote in message
...
Very cool. Now I have two additional variables to add to the equation.
First I only want 1 total number per week in column C. In this formula I
am
getting C1 and C2 with the Total count of 2 in it. I only want it to be
in
C1. Second is there a way I can paste new data into my columns and not
have
to redo the formula. Example would be next week when I past data I might
have 4 types in column 1 for wk 77 instead of 2.

"Ms-Exl-Learner" wrote:

Assume that you are having the Values in Column A & B Like the below:-

Col A Col B
Row1 a 77
Row2 b 77
Row3 c 78
Row4 d 80
Row5 e 80
Row6 81

In C1 cell paste the below formula
=SUMPRODUCT(($B$1:$B$100=$B1)*($A$1:$A$100<""))

Copy the C1 cell and paste it to the remaining cells of C Column.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Penny" wrote:

Need a formula.

Column 1 (type) Column 2 (wk)
a 77
b 77
c 78
d 80
e 80
blank 81

I would like the formula to come back for every week and tell me the
number
of types that are in that week. Another words in this case there are
qty 2
types in wk 77. In week 81 there are 0. In wk 78 there is 1 and week
80
there is 2.

Can anybody help?

Thanks.

__________ Information from ESET Smart Security, version of virus
signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Hi Penny

If you want a zero to appear, change the formula to
=IF(A2="",0,COUNTIF(Sheet1!B:B,A2))

If you say that there are no blanks or zero's appearing then the cells that
you think are blank, are probably not. They may contain a space character,
which will not be visible.
Try pressing delete on those cells in column A that you think should be
blank.

--
Regards
Roger Govier

"Penny" wrote in message
...
Great. This works perfect with one exception. When I have a blank field
and
it is not giving me 0 or blank.

"Roger Govier" wrote:

Hi Penny

Set up a new table (say on sheet 2) with just a list of week numbers in
column A starting at A2
Then in B2 enter
=IF(A2="","",COUNTIF(Sheet1!B:B,A2))
Copy down column B on Sheet2 as far as you wish.


--
Regards
Roger Govier

"Penny" wrote in message
...
Very cool. Now I have two additional variables to add to the equation.
First I only want 1 total number per week in column C. In this formula
I
am
getting C1 and C2 with the Total count of 2 in it. I only want it to
be
in
C1. Second is there a way I can paste new data into my columns and not
have
to redo the formula. Example would be next week when I past data I
might
have 4 types in column 1 for wk 77 instead of 2.

"Ms-Exl-Learner" wrote:

Assume that you are having the Values in Column A & B Like the below:-

Col A Col B
Row1 a 77
Row2 b 77
Row3 c 78
Row4 d 80
Row5 e 80
Row6 81

In C1 cell paste the below formula
=SUMPRODUCT(($B$1:$B$100=$B1)*($A$1:$A$100<""))

Copy the C1 cell and paste it to the remaining cells of C Column.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Penny" wrote:

Need a formula.

Column 1 (type) Column 2 (wk)
a 77
b 77
c 78
d 80
e 80
blank 81

I would like the formula to come back for every week and tell me the
number
of types that are in that week. Another words in this case there
are
qty 2
types in wk 77. In week 81 there are 0. In wk 78 there is 1 and
week
80
there is 2.

Can anybody help?

Thanks.

__________ Information from ESET Smart Security, version of virus
signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus
signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus
signature database 4839 (20100205) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4839 (20100205) __________

The message was checked by ESET Smart Security.

http://www.eset.com