SUM with Critera in 3 columns.
Results 1 to 6 of 6

Thread: SUM with Critera in 3 columns.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2014
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUM with Critera in 3 columns.

    Hello all. I having been given a spreadsheet that I have been told I can make minimal changes to and i need to get this formula to work if possible? If i cant would i just have to add a new column with the year in?

    The Formula ive been trying is =SUMPRODUCT(--('TASK DETAILS'!D1:D1000="2014"),--('TASK DETAILS'!E1:E5000="Jan"),--('TASK DETAILS'!AT1:AT1000="Y"))

    Which isnt working :/

    I have Three Columns with data in, and I only want it to count if if all 3 terms are met. Terms being "2014" in column D, "Jan" in column E and "Y" in column AT.

    I apolagise for the bad spelling and gramma.

    Your help would be so greatly received.

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Yorkshire, UK
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM with Critera in 3 columns.

    I think you need consistent row ranges, so either 1 to 1000 or 1 to 5000, but not a mixture of both. If that doesn't work, does this: =SUMPRODUCT(('TASK DETAILS'!D1:D5000="2014")*('TASK DETAILS'!E1:E5000="Jan")*('TASK DETAILS'!AT1:AT5000="Y"))

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM with Critera in 3 columns.

    Welcome to the board.

    All ranges need to be the same size.
    Quote Originally Posted by Chris1987 View Post
    =SUMPRODUCT(--('TASK DETAILS'!D1:D1000="2014"),--('TASK DETAILS'!E1:E5000="Jan"),--('TASK DETAILS'!AT1:AT1000="Y"))
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    Board Regular
    Join Date
    Dec 2009
    Location
    Yorkshire, UK
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM with Critera in 3 columns.

    If you are using excel 2007 or later, you could just use COUNTIFS: =COUNTIFS('TASK DETAILS'!D1:D5000,2014,'TASK DETAILS'!E1:E5000,"Jan",'TASK DETAILS'!AT1:AT5000,"Y")
    Last edited by a massive zebra; Oct 8th, 2014 at 09:26 AM.

  5. #5
    New Member
    Join Date
    Oct 2014
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM with Critera in 3 columns.

    Thank you so much! I cant believe it was that simple!

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM with Critera in 3 columns.

    Glad to help, thanks for the feedback.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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