Countif for different values

  • Thread starter Thread starter Sabrina
  • Start date Start date
S

Sabrina

I have a large spreadsheet that I am attempting to use the
countif function for various data. (Example, I first want
it to count all of the entries for Sally Jones and then I
want to count all of the entries for Joe Smith). I
created the countif formula for Sally Jones and now am
attempting to copy and paste that same formula to search
for Joe Smith; but when I do, the formula changes the
range of cells - from b2:t45 to b3:t46). I have tried the
Paste Special function to paste the formula; but it
continues to make this change. How can I get the formula
to remain static when I copy and paste it? HELP!
 
Hi Sabrina
just change b2:t45 to $B$2:$T$45

this makes the cell reference absolut (you have use relative
references)

HTH
Frank
 
Hi Sabrina!

Use the following structure:

I have my data in A1:A24 but note that I use an absolute reference in
my formula range
I have names in E1 and below

=COUNTIF($A$1:$A$24,"="&E1)

Without putting names in cells for use by COUNTIF the structure would
be:

=COUNTIF($A$1:$A$24,"="&"Norman")

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
=COUNTIF(A1:B200,{"Sally Jones","Joe Smith"}) array entered into a
two-column row. It will return the count of "Sally Jones" into the first
cell, "Joe Smith" into the second.

Alan Beban
 
Hi Norman

just wondering why you put "="& in the criteria bit .... it seems to
work just as well without it - what am i missing?

Regards
JulieD
 
Hi JulieD!

=COUNTIF($A$1:$A$24,"="E1)
Doesn't work

What are you getting to work OK?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi JulieD!

I guess you want:

=COUNTIF($A$1:$A$24,"Norman")

That works but it's not suitable for copying down to get counts for
others in E1 downwards.

I tend to avoid hard coding variables into formulas except for
examples and tests.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I assume =COUNTIF($A$1:$A$24,E1)

COUNTIF($A$1:$A$24,"="&E1) also works, but I guess her point was that
you don't need the equal sign (although her point is snipped in your
reply below).

Alan Beban
 
Hi Alan!

Agreed! The = is implied.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
news:[email protected]...
 
Sabrina,

This sounds like a good chance to learn about &/or exploit the wonders
of Pivot Tables.

You can write all the formulas you like but are unlikely to outdo this
"Power Tool" of Excel

David
 
Hi i'm STephen from the Philippines,

i'm using microsoft great plains and it is integrated with excel...m
problem now is how can i format the cell that after saving the exce
file the date that would appear will be the date during i saved it?

Thank you...

Stephe
 
Hi,

I'm trying to use a countif statement to calculate how many financ
deals have been done on a specific car.

=COUNTIF('Paul Gould'!F2:F16,"C",AND('Paul Gould'!M2:M16,">0"))

I've copied the idea form an example I have but it wont work. Need som
advise because I can't see what to do for looking at the problem!

Thanks

Ros
 
Hi
COUNTIF only accepts two parameters. Can you explain what you're trying
to achieve with some sample data rows (plain text, no attachment
please).
Maybe the following formula is what you want
=SUMPRODUCT(('Paul Gould'!F2:F16="C")*('Paul Gould'!M2:M16>0))
 
Back
Top