Formula in 07

[Penny]

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.

 

[Ms-Exl-Learner]

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!

 

[Penny]

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.

 

[Roger Govier]

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

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.



__________ 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

 

[Penny]

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

 

[Roger Govier]

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

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



__________ 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

 

[Penny]

Okay how about a whole different slant... If the Column A has a "0" in it it
should not be included in the total count that this formula is turning
around. Row 6 in the example below is blank... say it has a 0 in it but I
don't want that included in the count... Help please?????

Thanks.

 

[Roger Govier]

Hi Penny

If that is the case, and that there will be values in column B of Sheet1
when column A is blanks, then you will need to revert to the Sumproduct
formula given to you by Exl-Learner, but modified to work on Sheet2.
Enter in Sheet2 cell B2 the following
=IF($A2="","",SUMPRODUCT((Sheet1!$B$2:$B$1000=$A2)*(Sheet1!$A$2:$A$1000<>"")))
and copy down as required

--
Regards
Roger Govier

Okay how about a whole different slant... If the Column A has a "0" in it
it
should not be included in the total count that this formula is turning
around. Row 6 in the example below is blank... say it has a 0 in it but
I
don't want that included in the count... Help please?????

Thanks.





__________ Information from ESET Smart Security, version of virus
signature database 4849 (20100208) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4849 (20100208) __________

The message was checked by ESET Smart Security.

http://www.eset.com