+ Reply to Thread
Results 1 to 6 of 6

Sum function query

  1. #1
    Registered User
    Join Date
    05-19-2008
    Posts
    56

    Sum function query

    OK, nobody seems to know the answer to my question this morning and I reckon I've overcomplicated things. What I want to do now instead is calculate the sum of values in column B, depending on the values in column A.

    Example:

    Col a Col b
    a 25
    a 45
    a 65
    b 70
    c 50
    a 15
    b 20


    A function (DSUM?) would calculate the total for everything with an 'a' in column a.


    Can anybody help?

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    =SUMIF(A1:A10,"a",B1:B10)

  3. #3
    Registered User
    Join Date
    05-19-2008
    Posts
    56
    Cheers, I'll give that a go

  4. #4
    Registered User
    Join Date
    05-19-2008
    Posts
    56
    That works great, thanks.

    Can I add another step in to include/exclude records with a different value in a third cell?

    So:

    All records which are 'a' in col.a and 'y' in colc?

    ALSO, All records which are 'a' in col.a and null (or not null) in colc?

    Col a Col b Col c
    a 25 y
    a 45
    a 65 n
    b 70 y
    c 50
    a 15 y
    b 20


    Excuse my ignorance, I realise this is a very easy question, which i could handle in MS Access but I'm not good at all in Excel.

  5. #5
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    For those examples try SUMPRODUCT instead:

    =SUMPRODUCT((A1:A7="a")*(C1:C7="y")*(B1:B7))

    =SUMPRODUCT((A1:A7="a")*(C1:C7<>"")*(B1:B7))

  6. #6
    Registered User
    Join Date
    05-19-2008
    Posts
    56
    Brilliant, cheers for that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1