CountIF?

[Guest]

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?

 

[Harald Staff]

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

 

[Guest]

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]

So what is wrong with my solution ? Still confused, sorry.

Best wishes Harald

 

[Guest]

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

 

[Guest]

That did it, thanks to both of you for your time and efforts. What do the
(--) represent in the formulas below?