COUNTIF Formula

Discussion in 'Microsoft Office Specialist (MOS), Office 365' started by Rosy, Jul 10, 2003.

  1. Rosy
    Honorary Member

    Rosy Megabyte Poster

    665
    1
    54
    :cry:

    I am trying to count figures in a spreadsheet that DON'T equal the text "NONE" (I can't set this to 0 to do it as it is part of an assessment that I'm trying to do tutor copies for :oops: )

    At the moment I have got as far as =COUNTIF(K28:K41,"NONE") but I know that something is missing as I know that this counts the occurances of "NONE". Does anyone know what I am missing from this?????

    Hope this makes some form of sense. :oops:
     
    Certifications: MOS Master Instructor
  2. Phil
    Honorary Member

    Phil Gigabyte Poster

    1,680
    7
    87
    Hi Rosy

    you could give =COUNTIF(K28:K41,"<>NONE") a try, it seems to work for me.
     
    Certifications: MCSE:M & S MCSA:M CCNA CNA
    WIP: 2003 Upgrade, CCNA Upgrade
  3. Rosy
    Honorary Member

    Rosy Megabyte Poster

    665
    1
    54
    That works fine but if you have any blank rows in the range or delete a number from within the range then the total stays the same???
     
    Certifications: MOS Master Instructor
  4. Phil
    Honorary Member

    Phil Gigabyte Poster

    1,680
    7
    87
    ok, so you could try this

    =SUMPRODUCT((K28:K41<>"NONE")*(K28:K41>0))
     
    Certifications: MCSE:M & S MCSA:M CCNA CNA
    WIP: 2003 Upgrade, CCNA Upgrade
  5. Rosy
    Honorary Member

    Rosy Megabyte Poster

    665
    1
    54
    Thank you so much Phil!

    As I have to try to teach the formula :roll: the I think I will go for the first option that you gave me and hope that the students don't put empty bits into their spreadsheets :oops: !!!

    Thanks :wink:
     
    Certifications: MOS Master Instructor
  6. Rosy
    Honorary Member

    Rosy Megabyte Poster

    665
    1
    54
    Phil

    I just wanted to let you know that I have contacted our awarding body and given them the formula and they said it was the correct one so thank you very much for the help!

    I wish to award you expert points for your help!

    Rosy
     
    Certifications: MOS Master Instructor
  7. Phil
    Honorary Member

    Phil Gigabyte Poster

    1,680
    7
    87
    Thanks Rosy :) , I'm happy I was able to help. Which formula did the trick in the end? was it the countif which you said you were going to go with or sumproduct?
     
    Certifications: MCSE:M & S MCSA:M CCNA CNA
    WIP: 2003 Upgrade, CCNA Upgrade
  8. Rosy
    Honorary Member

    Rosy Megabyte Poster

    665
    1
    54
    It was the countif formula - it is only a level 2 qualification - I just have to ensure that students don't leave empty rows or it won't work!

    Can you break down the other formula for me though so that I can understand what I might use this for (in detail as s/sheets & their formulas are my weakness!)

    Cheers
     
    Certifications: MOS Master Instructor
  9. Sandy

    Sandy Ex-Member

    1,091
    2
    65
    I use the =DCOUNT command for this :wink:
     
  10. Rosy
    Honorary Member

    Rosy Megabyte Poster

    665
    1
    54
    What does a DCOUNT formula do? I am aware that it is a count function that works with numbers - does it count the cells or the numbers contained within them? Also can I tell it to count the cells without the text "NONE" in them?
     
    Certifications: MOS Master Instructor
  11. Phil
    Honorary Member

    Phil Gigabyte Poster

    1,680
    7
    87
    Hi Rosy
    This is how I understand sumproduct to work

    sumproduct(criteria)*(range of rows to add up based on criteria)

    so

    if we had a range of cars down column a and their values down column b

    a b
    1 ford 10,000
    2 bmw 20,000
    3 merc 30,000
    4 ford 10,000
    5 bmw 20,000

    sumproduct(a1:a5="ford")*(b1:b5)

    would give us the total value of ford cars in the list

    The way it works excel compares the criteria "ford" to each cell in the a column, if it

    finds a match it assigns a value of 1 to that cell, if not it assigns a 0. So in the

    background it has

    a b
    1 1 * 10,000 = 10,000
    2 0 * 20,000 = 0
    3 0 * 30,000 = 0
    4 1 * 10,000 = 10,000
    5 0 * 20,000 = 0

    so sumproduct(a1:a5="ford")*(b1:b5) = total value of fords in the list = 20,000


    But, you can also place criteria in the range of rows to add up so you'd have

    sumproduct(criteria)*(criteria)

    So to take our cars again, but with colours this time

    a b
    1 ford yellow
    2 ford red
    3 merc blue
    4 ford yellow
    5 bmw green

    so sumproduct(a1:a5="ford")*(b1:b5="yellow") will equate to


    a b
    1 1 * 1 = 1
    2 0 * 0 = 0
    3 0 * 0 = 0
    4 1 * 1 = 1
    5 0 * 0 = 0


    sumproduct(a1:a5="ford")*(b1:b5="yellow") = number of yellow fords = 2

    HTH
    Sorry having trouble getting the tables to space properly
     
    Certifications: MCSE:M & S MCSA:M CCNA CNA
    WIP: 2003 Upgrade, CCNA Upgrade
  12. Rosy
    Honorary Member

    Rosy Megabyte Poster

    665
    1
    54
    Thanks Phil - think I understand that now - thanks for the full explanation - expecially with my struggle to understand figures and formulas!

    You're a star!

    :wink:
     
    Certifications: MOS Master Instructor
  13. Sandy

    Sandy Ex-Member

    1,091
    2
    65
    DCOUNT

    Is a database function count cells with an entry in them numbers or text. Null cells are not counted.
     
  14. SimonV
    Honorary Member

    SimonV Petabyte Poster Gold Member

    6,651
    180
    258
    Extract from link below: (its a word doc)

    look here Rosy
     
    Certifications: MOS Master 2003, CompTIA A+, MCSA:M, MCSE
    WIP: Keeping CF Alive...

Share This Page

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.