Countif by Year

[Guest]

I need to count a cell if another cell is in a particular year. For example:
Column S has dates in this format (May-05). The dates range from Jan-96 to
Aug-05. The numbers to be added are in column W. So, I need a formula that
will calcuate a total for 1996, 1997, 1998, through 2005.

 

[Peo Sjoblom]

One way, assume dates in A2:A100 and the values you want to total in
B2:B100, adapt to fit your own ranges, if you meant you ant the total first
for 1996, then a total for 1997 and so on

=SUMPRODUCT(--(YEAR(A2:A100)=1996),B2:B100)

if you want all years from 1996 to 2005

=SUMPRODUCT(--(YEAR(A2:A100)>=1996),B2:B100)

it will only work if the dates are real dates and thus numerical

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

I need a similar formula as =SUMPRODUCT(--(YEAR(A2:A100)=1996),B2:B100), but
If A2:A100 = 1996, then it will count the total in cells AE2:AO86. I
substituted AE2:AO86 where B2:B100 is, but I get #VALUE!.

 

[Peo Sjoblom]

Change it to

=SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))



--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

I still get #VALUE!. FYI: The results in AE2:AO86 are "1's" in each cell,
so I was trying to count how many "1's" were in 1996. However, I guess I
could just look in column "T" for how many times 1996 occurs. Would it be
possible to just look in column "T" for the year 1996. Remember, my format
in column "T" is Jan-96.
I tried =COUNTIF(T2:T86,"96"), but the result was 0.

 

[Dave Peterson]

Do you have any #value! errors in any of those cells that are referred to in
your formula?

Are you positive that the values in T2:T86 are dates just formatted as Jan-96.
Or are those values just plain old text?

If you try:
=isnumber(t2)
do you get true (indicating that that one is a date) or do you get False
(indicating that that one is just plain old text).

If you have just plain old text, you could use:

=sumproduct(--(right(t2:t86,2)="96"))

 

[Guest]

I get a TRUE answer. They are all formatted as Jan-96. The dates range from
1996 to 2005.

 

[Dave Peterson]

Select those columns
Edit|goto|special
Click Constants
Uncheck: Numbers, text, logicals (keep Errors checked)

Did you find any errors?

Then do the same for formulas:
Edit|goto|special
Click Formulas
Uncheck: Numbers, text, logicals (keep Errors checked)

Did you find any errors?

====
If you're using this formula:
=SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))

Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
that range could be the problem, too.

 

[Guest]

I did not find any errors. A box popped up that said "No cells were found".
I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.

 

[Dave Peterson]

Ahhh. I didn't notice that you used AE2:AO86.

=SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))

You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
work.

I did not find any errors. A box popped up that said "No cells were found".
I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.

 

[Guest]

So, what you are saying is there is not a formula to count AE2:AO86. IS
there a way to just look in column "T" and count how many times 1996, 1997,
1998, and so on to 2005 occurs?

 

[Dave Peterson]

=sumproduct(--(year(t2:t86)=96))

Should work--if they're really dates and no errors <bg>.

So, what you are saying is there is not a formula to count AE2:AO86. IS
there a way to just look in column "T" and count how many times 1996, 1997,
1998, and so on to 2005 occurs?

 

[Guest]

I formatted column "T" to dates, to make sure again, but no luck. The below
fomula at least gives me a "0", I guess I will just have to count manually.
I give up.

 

[Dave Peterson]

Formatting the column as dates won't change the value in the cell--it'll only
change the display.

If you format that column to General, do all the cells change to a number?

If not, you still have text in that range.

I formatted column "T" to dates, to make sure again, but no luck. The below
fomula at least gives me a "0", I guess I will just have to count manually.
I give up.

 

[Guest]

Yes - When I format to General, they all change to a number.

 

[Dave Peterson]

Are you sure the data is in column T.

Do you have any hidden rows that might be affecting the formula?

(I'm almost out of guesses!)

Yes - When I format to General, they all change to a number.

 

[Guest]

It works now. For some reason when I was working on the spreadsheet
yesterday it returned a "0" answer. When I opened it up today and changed
the formula to look for 1997, it worked. So, I assume my spreadsheet had
some sort of corruption in it and when it was shut down and reopened, it
cleared it up. Sorry for wasting your time.

This is the formula that worked: =SUMPRODUCT(--(YEAR($T$2:$T$150)=1996))

 

[Dave Peterson]

Doh!

This formula:
=sumproduct(--(year(t2:t86)=96))
wouldn't work too well.

But
=sumproduct(--(year(t2:t86)=1996))
may work better!

(I screwed it up when I went from text to real dates! Sorry.)

It works now. For some reason when I was working on the spreadsheet
yesterday it returned a "0" answer. When I opened it up today and changed
the formula to look for 1997, it worked. So, I assume my spreadsheet had
some sort of corruption in it and when it was shut down and reopened, it
cleared it up. Sorry for wasting your time.

This is the formula that worked: =SUMPRODUCT(--(YEAR($T$2:$T$150)=1996))