• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help needed on formula

G

guitarman

Member
I have a large amount of data in five columns G,H,I,J,K, they are all numbers. What I want is a formula that will count all the Numbers below Four (4) and all the numbers above seven(7). I was thinking is it something like =SUMIF(G$1:K$310)<4 and =SUMIF(G$1:K$310)>7. As you have probably guessed I am no Einstein on these things.Any help would be really appreceiated.

Mike
 
Hello Guitarman,

You thoughts are rights, unfortunately the syntaw is not.


The correct syntax for this function is :


=SUMIF(G$1:K$310, "<4")


Modify for the other one.


Regards.
 
Hello Cyril

Many thanks for that. I thought it was something like that but not being very good at Excel I thought one of you experts would sort it again many thanks.Have a great day and a brilliant life.

Mike
 
Hi, guitarman!

Whenever being faced with a correct function but wrong syntax you can click on the "f(x)" button at the left of the input/edit cell bar/box, and you'll be prompted for the required parameters and the right formula will be retrieved.

Regards!
 
Hi SirJB7


many thanks for that info. But unfortunately the formula did not work as I had hoped I entered it and it returned a number of (395) for numbers below four (4) and I was amazed so I counted through them and there was only fourty one (41) so I don't know what happened there all I can assume is that is not the right formula for this task. I did make a mistake in the initial layout by putting =SUMIF(G$1:K$310,"310<4") it should have read =SUMIF(G$1:K$40,"310<4") I corrected that but it still came back with (254).Puzzled any Ideas what I have done wrong?

Mike
 
HI SirJB7


Sorry i Made a mistake in my last post the formula should read

=SUMIF(G$1:K$40,"<4") Not =SUMIF(G$1:K$40,"310<4")

Mike
 
Hi, guitarman!

Glad you solved it, but all the credit is for Cyril not for me, I only told you how to get the proper syntax for a function with the help of the "f(x)" Excel button.

Regards!
 
Hi SirJB7


Thanks anyway. Just one little queery is there a formula for the same thing. I would like to be more specific by asking it to find all numbers lower than four (4) but not greater than ten (10) so it would be something like =COUNTIF(G$2:K$40,"<4"?????> is this possible

Mike
 
Hi, guitarman!

If you're using an Excel version 2007 or 2010 then check for COUNTIFS function, it handles multiple criteria ranges.

Regards!
 
Hi SirJB7


Thank you for that i have worked it out even though Excel said I had an error in my formula would I like excel to correct it which I said yes and guess what it did and gave me a return of (0) in every cell wonderful anyhow I plodded on and came up with this formula =COUNTIFS(G$2:K$40,">9",G$2:K$40,"<20") which works fine. Thanks for your help

Have a good day and a brilliant life

Mike
 
Hi, guitarman!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top