Sumif for countif?

[pantelis]

Hi all,

Got a bit of a problem, I want to use the sumif comand as a countif i.e. I
have data with years 1990 to 2003 in column A and in column B I have various
numbers.

I want with a comand similar to sumif to show show me how many of the 1990
years make up the 1990 total.

Would appreciate your help.

Pantelis

 

[Paul B]

Pantelis, try this =SUMPRODUCT((YEAR(A1:A100)=1990)*(B1:B100))

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[pantelis]

Paul thanks for the response but will not work

1990 9932
1990 6918
1990 9125
1990 9270
1990 6239
1991 9156
1991 6161
1991 4089
1992 2021
1992 876
1992 2637
1992 5312
1992 8501


for example for these figures it should have shown me count=5 for 1990, 3
for 1991 etc.

I most probably have to define a funciton as code as I could have three or
four columns and the funtion should come back and show me the cound for 1990
in column 1, 2 & 3 say.

Would appreciate any other recommendations
Pantelis

 

[Steve Hieb]

Not sure if I'm missing something, but COUNTIF should work.

=COUNTIF(A:A,"1990") gave me 5

If you want the sum for 1990 use:

=SUMIF(A:A,1990,B:B) which gave me 41,484

Those aren't dates formated to look like years are they? Example:
real value is 1/1/1990 but shows up as "1990" due to formatting. If
so you need to change the formula to look for the whole date not just
the year.

HTH,
Steve Hieb

 

[Paul B]

Pantelis, I thought you had dates like 1-1-1990 in the column, if you just
have the year use one of the countif formulas that Dan gave you
--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **