Help with Excel 2007 Formula

[Marilyn]

=SUM(COUNTIF(D36606,"$L5$L6$L7"))+(COUNTIF(H36:H606,"hC"))
Trying to count the number of L5,L6,L7 in column range D36606, for a total
count. Then I want that total number of L5,L6,L7 (one sum)to display the
total hC count shown in column H36:H606. I can't seem to get the total
number of hC for L5,L6,L7.

 

[David Biddulph]

Well, to start with, I don't understand what you are trying to do with the
SUM function.
What is it that you want the SUM function to add to
COUNTIF(D36606,"$L5$L6$L7")?
If you don't understand what the SUM function does, look it up in Excel
help.

In the COUNTIF you are looking in cloumn D for the string "$L5$L6$L7", but I
have a feeling that you didn't want to do that.
If what you are trying to do is count the number of rows in which:
a column D contains either the value from L5 or the value from L6 or the
value from L7
and
b column H contains the striong "hC"
then you could try
=SUMPRODUCT(((D36606=$L5)+(D36606=$L6)+(D36606=$L7)),--(H36:H606="hC"))
if L5,L6,L7 contain no duplicates, or
=SUMPRODUCT(((D36606=$L5)+(D36606=$L6)+(D36606=$L7))>0,--(H36:H606="hC"))
if there might be duplicates.

 

[Geoff_L]

Not very clear what you are trying to do. Are you simply adding up the
total number of L5, L6, L7 and hC instances in the 2 columns (as your formula
would suggest)? Or is there more to it than that? To achieve the above then
the formula should read:

=SUM(COUNTIF(D36606,"=L5") + COUNTIF( D36606,"=L6") + COUNTIF( D36606,
"=L7") + COUNTIF(H36:H606, "=hC"))

Please explain more if this is not what you are trying to do.

If this was helpful, click Yes.

 

[David Biddulph]

I think you've missed something, Geoff.

One argument for your SUM function is
COUNTIF(D36606,"=L5") + COUNTIF( D36606,"=L6") + COUNTIF( D36606,
"=L7") + COUNTIF(H36:H606, "=hC")
but it isn't clear what you want to SUM to that.
Or did you intend your formula not to be
=SUM(COUNTIF(D36606,"=L5") + COUNTIF( D36606,"=L6") + COUNTIF( D36606,
"=L7") + COUNTIF(H36:H606, "=hC"))
but just
=COUNTIF(D36606,"=L5") + COUNTIF( D36606,"=L6") + COUNTIF( D36606,
"=L7") + COUNTIF(H36:H606, "=hC") ?

If you don't understand what the SUM function does, look it up in Excel
help.

And are you sure that the OP wanted for look for the text string L5 (or L6,
or L7)? I had assumed that she was looking in column D for a value equal to
the value in cell L5 or L6 or L7.
I agree with you that it wasn't clear what the OP was trying to do. I made
a guess in my reply to the thread, but we are all guessing.