+ Post New Thread
Results 1 to 9 of 9
Office Software Thread, COUNTIF Fuction in Technical; I have a excel spreadsheet I have a list of names going down in column A and data going down ...
  1. #1
    vunsev's Avatar
    Join Date
    Feb 2008
    Posts
    18
    Thank Post
    4
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    COUNTIF Fuction

    I have a excel spreadsheet I have a list of names going down in column A and data going down in column B.

    So basically I need to count the data in B but only if the name in column A is correct.
    Attached Images Attached Images

  2. #2

    Michael's Avatar
    Join Date
    Dec 2005
    Location
    Birmingham
    Posts
    9,262
    Thank Post
    242
    Thanked 1,568 Times in 1,250 Posts
    Rep Power
    340
    Counting up all data is straight forward:

    Code:
    =SUM(B1:B5)
    How do you want to verify the names are correct? It needs to reference against something.

  3. #3
    vunsev's Avatar
    Join Date
    Feb 2008
    Posts
    18
    Thank Post
    4
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I don't want to add up the contents of B column. I need to count it only if the names matches for example something like.

    =COUNTIF(A:A, "Peter") AND (B:B, "3").

    I only want it to count it if it matches both condtions.

    Didn't explain it very well in my previous post.

    Quote Originally Posted by Michael View Post
    Counting up all data is straight forward:

    Code:
    =SUM(B1:B5)
    How do you want to verify the names are correct? It needs to reference against something.

  4. #4

    Michael's Avatar
    Join Date
    Dec 2005
    Location
    Birmingham
    Posts
    9,262
    Thank Post
    242
    Thanked 1,568 Times in 1,250 Posts
    Rep Power
    340
    I've tried and I cannot seem to get anything working! Frustrating! Have you had a look at mrexcel.com?

  5. #5

    Join Date
    Jun 2007
    Location
    Sheppey, Kent
    Posts
    9
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi there,

    How about this:

    COUNTIF provides for one criteria. If we count column A, where vehicle type is Cars:

    =COUNTIF(A2:A10,”Cars”)

    But what if we want to count of records where vehicle type is cars and the color is black? We cannot use the COUNTIF function for multiple criteria.

    We need to use an array formula, which requires that we press Ctrl+Shift+Enter after typing it, instead of just Enter.

    =SUM((A2:A10=”Cars”)*(B2:B10=”Black”))

    Count of records with between 500 and 800 units sold.
    This also requires an array formula, press Ctrl+Shift+Enter after typing it.

    =SUM((C2:C10>=500)*(C2:C10<=800))

    Because these formulas are Array Formulas (sometimes referred to as CSE formulas because we must Ctrl+Shift+Enter to enter them), they look a little different. In the formula bar, after it's been properly entered, the above formula looks like this.

    {=SUM((C2:C10>=500)*(C2:C10<=800)) }

    Can't remember where I got this tip, but kudos to them...


    Gary

  6. #6

    Join Date
    Sep 2008
    Location
    Newcastle upon Tyne
    Posts
    17
    Thank Post
    0
    Thanked 1 Time in 1 Post
    Rep Power
    0
    In Office 2007 there is now a COUNTIFS function which, if I rememeber correctly, lets you supply more than one criteria. I haven't used it very much so can't give precise instruction but have a look and see if it helps.

    Graeme

  7. #7

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,763
    Thank Post
    3,273
    Thanked 1,053 Times in 974 Posts
    Rep Power
    365
    cant you use the following 3

    and if function to check if a vlookup is null or not null and then do the ifcount if the vlookup returns true or false or w/e and do it that way ?

  8. #8

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,156
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124
    If I understand what you're trying to do, you need to use DCOUNT

    Give your data range a name (makes the formula easier!), put the criterion you're using in a pair of cells with the field name on top, value underneath (eg cells E1 and E2):
    Name
    Peter
    Enter a formula:
    =dcount(datatable, 2,e1:e2)
    - this says count the cells in field 2 of datatable which have a number in them and where the value in "name" is "peter"

    If you want to count for name=peter and data=2 then you extend the criterion range:
    Name Data
    Peter 2
    and your formula becomes
    =dcount(datatable, 2,e1:f2)

    Not sure what you're doing but this might be getting to the point where it would be easier to do with Access or other database. Excel is good but querying data like this is better done with the proper tool :-)

  9. #9

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,763
    Thank Post
    3,273
    Thanked 1,053 Times in 974 Posts
    Rep Power
    365
    Quote Originally Posted by vunsev View Post
    I have a excel spreadsheet I have a list of names going down in column A and data going down in column B.

    So basically I need to count the data in B but only if the name in column A is correct.
    how will you know that the name in column A is correct ?

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 4
    Last Post: 14th August 2009, 01:58 AM
  2. Office 2007 at home, Office 2003 at school
    By kennysarmy in forum Windows
    Replies: 25
    Last Post: 13th January 2009, 01:31 PM
  3. Running Office 2003 and Office 2007
    By mrforgetful in forum Office Software
    Replies: 17
    Last Post: 12th June 2008, 01:11 PM
  4. MS Word 2003 (as part of Office 2003) Printing Problems...
    By markwilliamson2001 in forum Windows
    Replies: 2
    Last Post: 22nd November 2007, 01:06 PM
  5. office 2003 and outlook 2003
    By mac_shinobi in forum Windows
    Replies: 2
    Last Post: 9th May 2007, 06:18 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •