ExcelBanter - Help with countif

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Help with countif (https://www.excelbanter.com/excel-worksheet-functions/11005-help-countif.html)

Mike

Help with countif

Ok here is my code.

=COUNTIF('Crash data'!$K$2:$K$462,"=12.33")-COUNTIF('Crash
data'!$K$2:$K$462,"12.43")

This works fine. Problem that I am having is when I change the 12.33 (for
example) to a cell reference (say C5) and I change 12.43 to a reference it
does not work.

Any ideas?

Thanks.

Bob Phillips

=COUNTIF('Crash data'!$K$2:$K$462,"="&C5)-COUNTIF('Crash
data'!$K$2:$K$462,""&C6)

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mike" wrote in message
...
Ok here is my code.

=COUNTIF('Crash data'!$K$2:$K$462,"=12.33")-COUNTIF('Crash
data'!$K$2:$K$462,"12.43")

This works fine. Problem that I am having is when I change the 12.33 (for
example) to a cell reference (say C5) and I change 12.43 to a reference it
does not work.

Any ideas?

Thanks.

JulieD

Hi Mike

change the formula to

=COUNTIF('Crash data'!$K$2:$K$462,"="&C5)-COUNTIF('Crash
data'!$K$2:$K$462,""&C6)

Cheers
JulieD

"Mike" wrote in message
...
Ok here is my code.

=COUNTIF('Crash data'!$K$2:$K$462,"=12.33")-COUNTIF('Crash
data'!$K$2:$K$462,"12.43")

This works fine. Problem that I am having is when I change the 12.33 (for
example) to a cell reference (say C5) and I change 12.43 to a reference it
does not work.

Any ideas?

Thanks.

Peo Sjoblom

Try

=COUNTIF('Crash data'!$K$2:$K$462,"="&C5)-COUNTIF('Crash
data'!$K$2:$K$462,""&C6)

Regards,

Peo Sjoblom

"Mike" wrote:

Ok here is my code.

=COUNTIF('Crash data'!$K$2:$K$462,"=12.33")-COUNTIF('Crash
data'!$K$2:$K$462,"12.43")

This works fine. Problem that I am having is when I change the 12.33 (for
example) to a cell reference (say C5) and I change 12.43 to a reference it
does not work.

Any ideas?

Thanks.

Mike

This method worked out, thanks to all.

Next step. Now that this is working I also want sort by a year.

So if I sort all the items in this range, then I want to sort by a year?

Do I use a Count(If + the above function?

Thanks.

"Peo Sjoblom" wrote:

Try

=COUNTIF('Crash data'!$K$2:$K$462,"="&C5)-COUNTIF('Crash
data'!$K$2:$K$462,""&C6)

Regards,

Peo Sjoblom

"Mike" wrote:

Ok here is my code.

=COUNTIF('Crash data'!$K$2:$K$462,"=12.33")-COUNTIF('Crash
data'!$K$2:$K$462,"12.43")

This works fine. Problem that I am having is when I change the 12.33 (for
example) to a cell reference (say C5) and I change 12.43 to a reference it
does not work.

Any ideas?

Thanks.

JulieD

Hi Mike

i'm guessing you're not really wanting to "sort" as in data / sort ... but
add an additional criteria to your statement. If this is the case you'll
need to use the SUMPRODUCT function, e.g.

=SUMPRODUCT(--('Crash data'!$K$2:$K$462=C5),--('Crash
data'!$K$2:$K$462<C6),--(YEAR('Crash data'!H2:H159)=2004))

Cheers
JulieD

"Mike" wrote in message
...
This method worked out, thanks to all.

Next step. Now that this is working I also want sort by a year.

So if I sort all the items in this range, then I want to sort by a year?

Do I use a Count(If + the above function?

Thanks.

"Peo Sjoblom" wrote:

Try

=COUNTIF('Crash data'!$K$2:$K$462,"="&C5)-COUNTIF('Crash
data'!$K$2:$K$462,""&C6)

Regards,

Peo Sjoblom

"Mike" wrote:

Ok here is my code.

=COUNTIF('Crash data'!$K$2:$K$462,"=12.33")-COUNTIF('Crash
data'!$K$2:$K$462,"12.43")

This works fine. Problem that I am having is when I change the 12.33
(for
example) to a cell reference (say C5) and I change 12.43 to a reference
it
does not work.

Any ideas?

Thanks.

Peo Sjoblom

aIf you want to sore that range by year you can use a help column and a
formula like

=YEAR(K2)

copy down to K462 and select both columns and sort by the help column,
if you meant that you want to apply the constraints from your countif AND a
particular year you can use

=SUMPRODUCT(--('Crash data'!$K$2:$K$462=C5),--('Crash
data'!$K$2:$K$462<=C6),--(YEAR('Crash data'!$K$2:$K$462)=2004))

Regards,

Peo Sjoblom

"Mike" wrote:

This method worked out, thanks to all.

Next step. Now that this is working I also want sort by a year.

So if I sort all the items in this range, then I want to sort by a year?

Do I use a Count(If + the above function?

Thanks.

"Peo Sjoblom" wrote:

Try

=COUNTIF('Crash data'!$K$2:$K$462,"="&C5)-COUNTIF('Crash
data'!$K$2:$K$462,""&C6)

Regards,

Peo Sjoblom

"Mike" wrote:

Ok here is my code.

=COUNTIF('Crash data'!$K$2:$K$462,"=12.33")-COUNTIF('Crash
data'!$K$2:$K$462,"12.43")

This works fine. Problem that I am having is when I change the 12.33 (for
example) to a cell reference (say C5) and I change 12.43 to a reference it
does not work.

Any ideas?

Thanks.

Mike

What does the -- in between functions mean?

"JulieD" wrote:

Hi Mike

i'm guessing you're not really wanting to "sort" as in data / sort ... but
add an additional criteria to your statement. If this is the case you'll
need to use the SUMPRODUCT function, e.g.

=SUMPRODUCT(--('Crash data'!$K$2:$K$462=C5),--('Crash
data'!$K$2:$K$462<C6),--(YEAR('Crash data'!H2:H159)=2004))

Cheers
JulieD

"Mike" wrote in message
...
This method worked out, thanks to all.

Next step. Now that this is working I also want sort by a year.

So if I sort all the items in this range, then I want to sort by a year?

Do I use a Count(If + the above function?

Thanks.

"Peo Sjoblom" wrote:

Try

=COUNTIF('Crash data'!$K$2:$K$462,"="&C5)-COUNTIF('Crash
data'!$K$2:$K$462,""&C6)

Regards,

Peo Sjoblom

"Mike" wrote:

Ok here is my code.

=COUNTIF('Crash data'!$K$2:$K$462,"=12.33")-COUNTIF('Crash
data'!$K$2:$K$462,"12.43")

This works fine. Problem that I am having is when I change the 12.33
(for
example) to a cell reference (say C5) and I change 12.43 to a reference
it
does not work.

Any ideas?

Thanks.

Bob Phillips

See http://xldynamic.com/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mike" wrote in message
...
What does the -- in between functions mean?

"JulieD" wrote:

Hi Mike

i'm guessing you're not really wanting to "sort" as in data / sort ...
but
add an additional criteria to your statement. If this is the case
you'll
need to use the SUMPRODUCT function, e.g.

=SUMPRODUCT(--('Crash data'!$K$2:$K$462=C5),--('Crash
data'!$K$2:$K$462<C6),--(YEAR('Crash data'!H2:H159)=2004))

Cheers
JulieD

"Mike" wrote in message
...
This method worked out, thanks to all.

Next step. Now that this is working I also want sort by a year.

So if I sort all the items in this range, then I want to sort by a
year?

Do I use a Count(If + the above function?

Thanks.

"Peo Sjoblom" wrote:

Try

=COUNTIF('Crash data'!$K$2:$K$462,"="&C5)-COUNTIF('Crash
data'!$K$2:$K$462,""&C6)

Regards,

Peo Sjoblom

"Mike" wrote:

Ok here is my code.

=COUNTIF('Crash data'!$K$2:$K$462,"=12.33")-COUNTIF('Crash
data'!$K$2:$K$462,"12.43")

This works fine. Problem that I am having is when I change the
12.33
(for
example) to a cell reference (say C5) and I change 12.43 to a
reference
it
does not work.

Any ideas?

Thanks.

All times are GMT +1. The time now is 04:01 AM.