Home |
Search |
Today's Posts |
#1
|
|||
|
|||
CountIF?
I'm wanting to have a box count the total number of times a C and W appear, C
and L, P and W, and P and L. I've tried using the CountIF, and can get the proper result for how many times one character shows up in one column (aka number of W's), but am trying to get a result where everytime a C and W appear, it will add one to the C and W box (where ? are located below.) The current formulas I've tried are... =COUNTIF(E4:E180,"C")&COUNTIF(I4:I48,"W") =COUNT(IF(E4:E180="C",1,0),IF(I4:I48="W",1,0)) C P&C 03/03/2005 30,000 W P EBS 03/04/2005 50,000 W P EBS C P&C (225,000) L P P&C W P Env. 04/20/2005 C P&C 03/24/2005 W Running Totals W L # Clients ? ? Any ideas of how to link these two columns together to dispaly a result in a different cell? -- Thanks, Warren |
#2
|
|||
|
|||
Ifg I understand this: The easy way out, for setup and maintenance, is a
helper column. Let us for this humble sample select column K. Enter in K4 =E4&I4 fill down, and do either a countif or a pivot table on that column. HTH. Best wishes Harald "Warren1872" skrev i melding ... I'm wanting to have a box count the total number of times a C and W appear, C and L, P and W, and P and L. I've tried using the CountIF, and can get the proper result for how many times one character shows up in one column (aka number of W's), but am trying to get a result where everytime a C and W appear, it will add one to the C and W box (where ? are located below.) The current formulas I've tried are... =COUNTIF(E4:E180,"C")&COUNTIF(I4:I48,"W") =COUNT(IF(E4:E180="C",1,0),IF(I4:I48="W",1,0)) C P&C 03/03/2005 30,000 W P EBS 03/04/2005 50,000 W P EBS C P&C (225,000) L P P&C W P Env. 04/20/2005 C P&C 03/24/2005 W Running Totals W L # Clients ? ? Any ideas of how to link these two columns together to dispaly a result in a different cell? -- Thanks, Warren |
#3
|
|||
|
|||
Hi Harald, sorry for the confusion.
For this example, I'd want the cell for C and W to display the number 2 (two instances where the letter C in column A and W in Column E appear together. For the cell for P and W, the number 3 would appear. For C and L, the number 3 would appear, and for P and L, the number 0. Column A Column E C P&C 03/03/2005 30,000 W P EBS 03/04/2005 50,000 W P EBS C P&C (225,000) L P P&C W P Env. 04/20/2005 W C P&C 03/24/2005 W C P&C 04/08/2005 P P EBS 03/04/2005 C OCIP 04/05/2005 L P P&C 05/05/2005 P P&C 04/13/2005 C P&C 04/15/2005 (85,000) L My side chart would then display, Running Totals W L # Clients 2 3 # Prospects 3 0 But wanting the formulas in the above cells to be in tact so I don't have to do manaully for the entire list and keep it updated by simply counting. Thanks! "Harald Staff" wrote: Ifg I understand this: The easy way out, for setup and maintenance, is a helper column. Let us for this humble sample select column K. Enter in K4 =E4&I4 fill down, and do either a countif or a pivot table on that column. HTH. Best wishes Harald "Warren1872" skrev i melding ... I'm wanting to have a box count the total number of times a C and W appear, C and L, P and W, and P and L. I've tried using the CountIF, and can get the proper result for how many times one character shows up in one column (aka number of W's), but am trying to get a result where everytime a C and W appear, it will add one to the C and W box (where ? are located below.) The current formulas I've tried are... =COUNTIF(E4:E180,"C")&COUNTIF(I4:I48,"W") =COUNT(IF(E4:E180="C",1,0),IF(I4:I48="W",1,0)) C P&C 03/03/2005 30,000 W P EBS 03/04/2005 50,000 W P EBS C P&C (225,000) L P P&C W P Env. 04/20/2005 C P&C 03/24/2005 W Running Totals W L # Clients ? ? Any ideas of how to link these two columns together to dispaly a result in a different cell? -- Thanks, Warren |
#4
|
|||
|
|||
So what is wrong with my solution ? Still confused, sorry.
Best wishes Harald "Warren1872" skrev i melding ... Hi Harald, sorry for the confusion. For this example, I'd want the cell for C and W to display the number 2 (two instances where the letter C in column A and W in Column E appear together. For the cell for P and W, the number 3 would appear. For C and L, the number 3 would appear, and for P and L, the number 0. Column A Column E C P&C 03/03/2005 30,000 W P EBS 03/04/2005 50,000 W P EBS C P&C (225,000) L P P&C W P Env. 04/20/2005 W C P&C 03/24/2005 W C P&C 04/08/2005 P P EBS 03/04/2005 C OCIP 04/05/2005 L P P&C 05/05/2005 P P&C 04/13/2005 C P&C 04/15/2005 (85,000) L My side chart would then display, Running Totals W L # Clients 2 3 # Prospects 3 0 But wanting the formulas in the above cells to be in tact so I don't have to do manaully for the entire list and keep it updated by simply counting. Thanks! "Harald Staff" wrote: Ifg I understand this: The easy way out, for setup and maintenance, is a helper column. Let us for this humble sample select column K. Enter in K4 =E4&I4 fill down, and do either a countif or a pivot table on that column. HTH. Best wishes Harald "Warren1872" skrev i melding ... I'm wanting to have a box count the total number of times a C and W appear, C and L, P and W, and P and L. I've tried using the CountIF, and can get the proper result for how many times one character shows up in one column (aka number of W's), but am trying to get a result where everytime a C and W appear, it will add one to the C and W box (where ? are located below.) The current formulas I've tried are... =COUNTIF(E4:E180,"C")&COUNTIF(I4:I48,"W") =COUNT(IF(E4:E180="C",1,0),IF(I4:I48="W",1,0)) C P&C 03/03/2005 30,000 W P EBS 03/04/2005 50,000 W P EBS C P&C (225,000) L P P&C W P Env. 04/20/2005 C P&C 03/24/2005 W Running Totals W L # Clients ? ? Any ideas of how to link these two columns together to dispaly a result in a different cell? -- Thanks, Warren |
#5
|
|||
|
|||
For your running total for 'c' and 'w' use
=SUMPRODUCT(--((A1:A100)="c"), --((E1:E100)="w")) For 'c' and 'l' use =SUMPRODUCT(--((A1:A100)="c"), --((E1:E100)="l")) For 'p' and 'w' use =SUMPRODUCT(--((A1:A100)="p"), --((E1:E100)="w")) For 'p' and 'l' use =SUMPRODUCT(--((A1:A100)="c"), --((E1:E100)="l")) HTH "Warren1872" wrote: I'm wanting to have a box count the total number of times a C and W appear, C and L, P and W, and P and L. I've tried using the CountIF, and can get the proper result for how many times one character shows up in one column (aka number of W's), but am trying to get a result where everytime a C and W appear, it will add one to the C and W box (where ? are located below.) The current formulas I've tried are... =COUNTIF(E4:E180,"C")&COUNTIF(I4:I48,"W") =COUNT(IF(E4:E180="C",1,0),IF(I4:I48="W",1,0)) C P&C 03/03/2005 30,000 W P EBS 03/04/2005 50,000 W P EBS C P&C (225,000) L P P&C W P Env. 04/20/2005 C P&C 03/24/2005 W Running Totals W L # Clients ? ? Any ideas of how to link these two columns together to dispaly a result in a different cell? -- Thanks, Warren |
#6
|
|||
|
|||
That did it, thanks to both of you for your time and efforts. What do the
(--) represent in the formulas below? "ww" wrote: For your running total for 'c' and 'w' use =SUMPRODUCT(--((A1:A100)="c"), --((E1:E100)="w")) For 'c' and 'l' use =SUMPRODUCT(--((A1:A100)="c"), --((E1:E100)="l")) For 'p' and 'w' use =SUMPRODUCT(--((A1:A100)="p"), --((E1:E100)="w")) For 'p' and 'l' use =SUMPRODUCT(--((A1:A100)="c"), --((E1:E100)="l")) HTH "Warren1872" wrote: I'm wanting to have a box count the total number of times a C and W appear, C and L, P and W, and P and L. I've tried using the CountIF, and can get the proper result for how many times one character shows up in one column (aka number of W's), but am trying to get a result where everytime a C and W appear, it will add one to the C and W box (where ? are located below.) The current formulas I've tried are... =COUNTIF(E4:E180,"C")&COUNTIF(I4:I48,"W") =COUNT(IF(E4:E180="C",1,0),IF(I4:I48="W",1,0)) C P&C 03/03/2005 30,000 W P EBS 03/04/2005 50,000 W P EBS C P&C (225,000) L P P&C W P Env. 04/20/2005 C P&C 03/24/2005 W Running Totals W L # Clients ? ? Any ideas of how to link these two columns together to dispaly a result in a different cell? -- Thanks, Warren |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions | |||
combining countif formulas | Excel Worksheet Functions |