Excel 2007 : COUNTIF issues + Another question

[systemcell]

Hello everyone

I have just started playing around with excel last night and so far it hasnt been difficult dealing with it and finding info on the internet about stuff but now i seem to have hit a dead end.

I have come to a point where i need to make a formula which will search throught a column and find all the numbers higher than 1000 and return the total number or cells with such numbers but heres the problem :

I have tried =COUNTIF(D6:D20, "">= 1000) but it just keeps returning 0 :\
I have also tried =COUNTA(D6:D20, "">= 1000) and for some reason it returns 16 while there are only 15 cells in the column and only 8 of those cells have values higher than 1000.

I would be very thankfull for any help you guys can offer

[davesexcel]

=COUNTIF(D6:D20,">1000")
May be what is missing.

[systemcell]

LOL YES thats it !

I saw the command on the net with ">1000 but excel it self suggested a fix and remade it to "">1000 for some reason.. and i was dumb enough not to second guess the program :P

Anyway tyvm again man Now its back to work ^^

[systemcell]

Hello again. Didnt want to make a new thread so i just edited this one since its partially related to the previous question

I have found all numbers >=1000 and counted them with COUNTIF now im wondering if there is a way to sum up all the extra values that go over 1000.

Let me explain. For example if the column shows the following numbers:

1437
1213
1110
910

Is it possible to work out a formula that would, if the number is higher than 1000, take the value that is over 1000 (437 in the first one) and then continue taking them from all the cells in the column that are higher than 1000 and sum them all up ?

I hope i explained it well enough

[teylyn]

How about

=SUMIF(A1:A4,">1000")-(COUNTIF(A1:A4,">1000")*1000)

adjust ranges to suit.

[systemcell]

Works awesome !

Thanks a lot man Cheers