CountIF & year

  • Thread starter Thread starter imadodat
  • Start date Start date
I

imadodat

I need to count the number of cells where the date in the a range of
cells is is equal to a certain year. The cells are formatted as a
date.

Example:

1-1-1976
2-3-1976
7-9-1975
9-7-1973
3-4-1976

========
If counting cells that is 1976 then the result should be:

3
 
The use of "--" in conjunction with the Sumproduct() is beginning to appear
more and more often of late. What does it (the "--") mean?
TIA,

Aladin Akyurek said:
=SUMPRODUCT(--(YEAR(A1:A5)=1976))
Click to expand...
 
(YEAR(A1:A5)=1976)

is a statement, which can be either
TRUE or FALSE, but SUMPRODUCT
can only use numbers,
so TRUE and FALSE must be converted
to their numerical equivalencies, which
are TRUE = 1 and FALSE = 0. This
conversion is done automathically by Excel
when TRUE and FALSE are involved in
a calculation. It could be

(YEAR(A1:A5)=1976) * 1
(YEAR(A1:A5)=1976) + 0

or, as in your case, a double negation.

It's one of Harlan´s smarties, which seems
to be the trend at the moment :-)


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

JMay said:
The use of "--" in conjunction with the Sumproduct() is beginning to appear
more and more often of late. What does it (the "--") mean?
TIA,
Click to expand...
 
Well, -- is a tiny bit faster than +0, -- and +0 are both faster than *1 and
other coercers.
 
Tks Leo


Leo Heuser said:
(YEAR(A1:A5)=1976)

is a statement, which can be either
TRUE or FALSE, but SUMPRODUCT
can only use numbers,
so TRUE and FALSE must be converted
to their numerical equivalencies, which
are TRUE = 1 and FALSE = 0. This
conversion is done automathically by Excel
when TRUE and FALSE are involved in
a calculation. It could be

(YEAR(A1:A5)=1976) * 1
(YEAR(A1:A5)=1976) + 0

or, as in your case, a double negation.

It's one of Harlan´s smarties, which seems
to be the trend at the moment :-)


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
Click to expand...
 
Normally it is not needed, as the SUMPRODUCT function will be testing
multiple ranges, or will test one range and multiply this by a range of
values, and so the TRUE FALSE values are coerced to number directly. When
you have just a singleton test, the coercion is required.

As Aladin says, -- is the quickest, and is probably arising more because
Harlan Grove, our very own efficiency champion, has bullied us all into
using it <vbg>. My view was that *1 was always more self-explanatory, but it
is becoming so common now that I have also adopted it (of course more common
may only apply to a very small audience, and the majority of recipients of
my workbooks are probably perplexed.).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

JMay said:
The use of "--" in conjunction with the Sumproduct() is beginning to appear
more and more often of late. What does it (the "--") mean?
TIA,
Click to expand...
 
Bob Phillips said:
As Aladin says, -- is the quickest, and is probably arising more because
Harlan Grove, our very own efficiency champion, has bullied us all into
using it <vbg>. . . .
Click to expand...

I haven't bullied anyone other than you. Are you so easily bullied?
. . . My view was that *1 was always more self-explanatory, . . .
Click to expand...

You never convinced me of this. Obviously it's subjective, so what works
well for some may not work as well for others. If self-documenting formulas
are the goal, none of the operator coersions is as clear as N(..). While
N(..) would use up a nested funtion call level, if you've reached the 7
nested call limit, you've already sacrificed simplicity and ease of
understanding.
 
I need to count the number of cells where the date in the a range of
cells is is equal to a certain year. The cells are formatted as a
date.

Example:

1-1-1976
2-3-1976
7-9-1975
9-7-1973
3-4-1976

========
If counting cells that is 1976 then the result should be:

3
Click to expand...



Greetings...
I do feel like a doofus!! I created this spreadsheet about two years
ago and havn't had to make any changes to it.... Now I do and can't
remember why I did certain things (I know a lot of the help came from
this newsgroup!!) Now I'm trying to go back and apply a filter for
the year just as this thread is describing.

My Spreadsheet uses seveal sheets, and is used to track and summarize
my wife's Real Estate transactions by Month. The summeries are
mainly by Month and are counts of contracts, listings etc.... and
dollar amounts such as contract price and commissions etc...

Some transactions cross year bounderies... Listed in 2003 and the
contract and closing is in 2004.

I am trying to apply the solutions from this thread, but having
trouble implementing it..

Question1:
I want to add a cell where I can enter a year to report on(EG 2003.
I don't know what format that cell should be... should it be a Number
or a Text field.?

Question2:
I have found a difference in the Formulas used for the Month of
January and the rest of the Months...

For example I go through the Details sheet and the following cell
gives me a "Under Contract" count.
For January
=SUMPRODUCT((MONTH(Details!F5:F150)=1)*(YEAR(Details!F5:F150)<>1900)*(Details!M5:M150=""))

For February (and other months)
=SUMPRODUCT((MONTH(Details!K5:K150)=2)*(Details!M5:M150 = ""))

I remember at the time having a heck of a problem in January that was
fixed by a suggestion from someone in this newsgroup to add the <>1900
to the January Formula... and it works great.....

In both of these cases, how can I add the "filter" from another cell
that contains a year... 2003 ( in Cell B2 for example...)

I have tried all sorts of ways to add the cell compare to B2 to both
formulas without success. ( When I was attempting to do this, I was
adding the -- to the Sumproduct as this thread indicates.

I also have the same problem with Dollar summaries..
EG
=SUM(IF(((MONTH(Details!K5:K150)=1)*(YEAR(Details!K5:K150)<>1900)*(Details!M5:M150
= "")),Details!W5:W150))


Thanks
John
 
A shorter way to do the first formula would be


=SUMPRODUCT((MONTH(Details!F5:F150)=1)*(Details!F5:F150<>"")*(Details!M5:M15
0=""))

using unary minuses

=SUMPRODUCT(--(MONTH(Details!F5:F150)=1),--(Details!F5:F150<>""),--(Details!
M5:M150=""))



1.

=SUMPRODUCT((MONTH(Details!F5:F150)=1)*(YEAR(Details!F5:F150)=B2)*(Details!M
5:M150=""))

or

=SUMPRODUCT(--(MONTH(Details!F5:F150)=1),--(YEAR(Details!F5:F150)=B2),--(Det
ails!M5:M150=""))

2.

=SUMPRODUCT((MONTH(Details!F5:F150)=2)*(YEAR(Details!F5:F150)=B2)*(Details!M
5:M150=""))

or

=SUMPRODUCT(--(MONTH(Details!F5:F150)=2),--(YEAR(Details!F5:F150)=B2),--(Det
ails!M5:M150=""))


3.

=SUMPRODUCT((MONTH(Details!K5:K150)=1)*(Details!K5:K150<>"")*(Details!M5:M15
0=""),Details!W5:W150)

or

=SUMPRODUCT(--(MONTH(Details!K5:K150)=1),--(Details!K5:K150<>""),--(Details!
M5:M150=""),Details!W5:W150)


will total values in W5:W150 where date in K5:K150 is January and M5:M150 is
blank

--

Regards,

Peo Sjoblom

Greetings...
I do feel like a doofus!! I created this spreadsheet about two years
ago and havn't had to make any changes to it.... Now I do and can't
remember why I did certain things (I know a lot of the help came from
this newsgroup!!) Now I'm trying to go back and apply a filter for
the year just as this thread is describing.

My Spreadsheet uses seveal sheets, and is used to track and summarize
my wife's Real Estate transactions by Month. The summeries are
mainly by Month and are counts of contracts, listings etc.... and
dollar amounts such as contract price and commissions etc...

Some transactions cross year bounderies... Listed in 2003 and the
contract and closing is in 2004.

I am trying to apply the solutions from this thread, but having
trouble implementing it..

Question1:
I want to add a cell where I can enter a year to report on(EG 2003.
I don't know what format that cell should be... should it be a Number
or a Text field.?

Question2:
I have found a difference in the Formulas used for the Month of
January and the rest of the Months...

For example I go through the Details sheet and the following cell
gives me a "Under Contract" count.
For January
Click to expand...
 
As always!!! Thank you!!!
Not only for the posts to my question, but for all the posts to
questions that are already posted here when I am looking for the
answer to the same question.. ( I hope that made sense! I had to read
it twice!)
Thanks Peo

John
 
Back
Top