Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help!!!
I have a worksheet w/column named "Reject reason" in Column A2:B10.
D & E collumn must be filled automatically when I entered "REJECT REASON" & "COMP?"(see (2)=). ------------------------------------------------------------------------------------------------ (1)= A B D E "Code #" "Rejects Reasons" "Comp A" "Comp B" 1 Contract 2 Ethnic Channels 3 Install probs 4 NFL 5 No Pitch 6 No Computer 7 Other Programming not avail 8 Owes us money 9 Previous service problems 10 Price too high ------------------------------------------------------------------------- (2)= In a cell range H10:I20, I have the Following:These values I have to entered myself. H I "REJECT REASON" "COMP?" 1 A 5 A 10 B 7 A 3 B 5 B 1 A 5 B ----------------------------------------------------------------------------------------- Ok, The collums D & E must count how many "Reject reasons" for each A 0r B Comp?... In this case it should look like this: A B D E "Code #" "Rejects Reasons" "Comp A" "Comp B" 1 Contract 2 2 Ethnic Channels 3 Install probs 1 4 NFL 5 No Pitch 1 2 6 No Computer 7 Other Programming not avail 1 8 Owes us money 9 Previous service problems 10 Price too high 1 ---------------------------------------------------------------------------------------------- I looks simple, but I just don't know how to do it. Pls help me. Thanks all. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help!!!
Assuming your source table is within A1:B11
with the data you enter within H10:I20 (in the same sheet) First, put in/change the col labels in C1:D1 to just: A, B Then place in C2: =SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2)) Copy C2 to D2, fill down to D11 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "OKY" wrote: I have a worksheet w/column named "Reject reason" in Column A2:B10. D & E collumn must be filled automatically when I entered "REJECT REASON" & "COMP?"(see (2)=). ------------------------------------------------------------------------------------------------ (1)= A B D E "Code #" "Rejects Reasons" "Comp A" "Comp B" 1 Contract 2 Ethnic Channels 3 Install probs 4 NFL 5 No Pitch 6 No Computer 7 Other Programming not avail 8 Owes us money 9 Previous service problems 10 Price too high ------------------------------------------------------------------------- (2)= In a cell range H10:I20, I have the Following:These values I have to entered myself. H I "REJECT REASON" "COMP?" 1 A 5 A 10 B 7 A 3 B 5 B 1 A 5 B ----------------------------------------------------------------------------------------- Ok, The collums D & E must count how many "Reject reasons" for each A 0r B Comp?... In this case it should look like this: A B D E "Code #" "Rejects Reasons" "Comp A" "Comp B" 1 Contract 2 2 Ethnic Channels 3 Install probs 1 4 NFL 5 No Pitch 1 2 6 No Computer 7 Other Programming not avail 1 8 Owes us money 9 Previous service problems 10 Price too high 1 ---------------------------------------------------------------------------------------------- I looks simple, but I just don't know how to do it. Pls help me. Thanks all. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help!!!
Max ,
I made couple changes and it works great. Thank you very much. I noticed that on the cells where nothing is entered a zero shows up. How do I get rid of if? I need something to make the cells C and D blank when no text is input in the H:I range. That will be my last request. thanks max. "Max" wrote: Assuming your source table is within A1:B11 with the data you enter within H10:I20 (in the same sheet) First, put in/change the col labels in C1:D1 to just: A, B Then place in C2: =SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2)) Copy C2 to D2, fill down to D11 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "OKY" wrote: I have a worksheet w/column named "Reject reason" in Column A2:B10. D & E collumn must be filled automatically when I entered "REJECT REASON" & "COMP?"(see (2)=). ------------------------------------------------------------------------------------------------ (1)= A B D E "Code #" "Rejects Reasons" "Comp A" "Comp B" 1 Contract 2 Ethnic Channels 3 Install probs 4 NFL 5 No Pitch 6 No Computer 7 Other Programming not avail 8 Owes us money 9 Previous service problems 10 Price too high ------------------------------------------------------------------------- (2)= In a cell range H10:I20, I have the Following:These values I have to entered myself. H I "REJECT REASON" "COMP?" 1 A 5 A 10 B 7 A 3 B 5 B 1 A 5 B ----------------------------------------------------------------------------------------- Ok, The collums D & E must count how many "Reject reasons" for each A 0r B Comp?... In this case it should look like this: A B D E "Code #" "Rejects Reasons" "Comp A" "Comp B" 1 Contract 2 2 Ethnic Channels 3 Install probs 1 4 NFL 5 No Pitch 1 2 6 No Computer 7 Other Programming not avail 1 8 Owes us money 9 Previous service problems 10 Price too high 1 ---------------------------------------------------------------------------------------------- I looks simple, but I just don't know how to do it. Pls help me. Thanks all. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help!!!
1. Easiest option is to suppress extraneous zeros display in the sheet:
Click Tools Options View tab Uncheck "Zero values" OK, done! 2. An alternative is to use a simple IF** construct, eg: =IF(SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2) )=0,"",SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$ A2))) **the downside is this will increase the calc load and may affect performance especially for heavy duty functions like sumproduct where large ranges are involved. Although in your case here, it shouldn't be material since the ranges are small. Your choice .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "OKY" wrote in message ... Max , I made couple changes and it works great. Thank you very much. I noticed that on the cells where nothing is entered a zero shows up. How do I get rid of if? I need something to make the cells C and D blank when no text is input in the H:I range. That will be my last request. thanks max. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help!!!
"Max" wrote...
.... 2. An alternative is to use a simple IF** construct, eg: =IF(SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2 ))=0,"", SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2)) ) **the downside is this will increase the calc load . . . "OKY" wrote in message .... I noticed that on the cells where nothing is entered a zero shows up.... .... Looks like all that needs testing is whether there's anything in H10:I20, so all that may take is =IF(COUNTA($H$10:$I$20),SUMPRODUCT(($I$10:$I$20=C$ 1)*($H$10:$H$20=$A2)),"") which should have much less impact on recalc speed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|