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: Roger Govier on 5 Feb 2010 17:50 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" <Penny(a)discussions.microsoft.com> wrote in message news:9C22AD7D-4A07-4A65-9E3F-A617ED022373(a)microsoft.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" <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 >> >> >> > > __________ 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
From: Penny on 8 Feb 2010 11:55 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" wrote: > 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" <Penny(a)discussions.microsoft.com> wrote in message > news:9C22AD7D-4A07-4A65-9E3F-A617ED022373(a)microsoft.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" <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 > >> > >> > >> > > > > __________ 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 > > >
From: Roger Govier on 9 Feb 2010 04:27
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 "Penny" <Penny(a)discussions.microsoft.com> wrote in message news:2F370890-C8C1-4CAC-A44F-79BBC34C1014(a)microsoft.com... > 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" wrote: > >> 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" <Penny(a)discussions.microsoft.com> wrote in message >> news:9C22AD7D-4A07-4A65-9E3F-A617ED022373(a)microsoft.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" <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 >> >> >> >> >> >> >> > >> > __________ 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 >> >> >> > > __________ 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 |