| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula in 07
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula in 07
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula in 07
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula in 07
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula in 07
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula in 07
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula in 07
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" 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula in 07
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" wrote in message ... 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" 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 __________ 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
