Countif for different values

[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!

 

[Frank Kabel]

Hi Sabrina
just change b2:t45 to $B$2:$T$45

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

HTH
Frank

 

[Norman Harker]

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.

 

[Alan Beban]

=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

 

[Julie]

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

 

[Norman Harker]

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.

 

[Norman Harker]

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.

 

[Alan Beban]

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

 

[Norman Harker]

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]...

 

[David Byrne]

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

 

[none12323]

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

 

[Ross Harries]

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

 

[Frank Kabel]

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))