Formula in 07
in [General]
|
Prev: Changing grey column headings from numbers back to capital letters
Next: I am attempting to find the difference between two dates...
From: Penny on 4 Feb 2010 11:44 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.
From: Ms-Exl-Learner on 4 Feb 2010 12:29 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.
From: Penny on 4 Feb 2010 12:56 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.
From: Roger Govier on 4 Feb 2010 15:43 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" <Penny(a)discussions.microsoft.com> wrote in message news:26EBF295-1B02-4B36-9335-02BD9BC206C5(a)microsoft.com... > 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
From: Penny on 5 Feb 2010 08:44
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" <Penny(a)discussions.microsoft.com> wrote in message > news:26EBF295-1B02-4B36-9335-02BD9BC206C5(a)microsoft.com... > > 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 > > > |