Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Can I get a Sum From All Values in a column that have a specific value in the column next to them?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2016
    Location
    Washington, DC
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    So, I'd like to have a list of transactions, and have each transaction be labeled something. Next to this list I'd like to have a total value of each transaction with a specific label. Like, if we were totally an amount spend on an event, we could say "$5" "Promotions" and "$10" "Services" etc. etc., and in the table next to that, it would, in a single cell, add the total value assigned each label, like it shows below.

    What would I put in the cell shown below to get the total value of everything labeled A?


    Thanks in advance for your help, and I apologize if this has been asked/answered, I tried searching for it, but I just don't know how to look or what to call it.



    date item value Total Values Based on Item
    1-Sep A 1 A HOW DO I GET THIS CELL?
    2-Sep C 2 B
    3-Sep D 1 C
    4-Sep A 2 D
    1-Sep B 3 E
    2-Sep E 4
    3-Sep C 3
    4-Sep D 1
    1-Sep C 2
    2-Sep A 2
    3-Sep C 1
    4-Sep A 2

  2. #2
    Board Regular Weazel's Avatar
    Join Date
    Dec 2011
    Location
    Florida
    Posts
    3,155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    something like...

    F2=SUMIF($B$2:$B$13,E2,$C$2)

    Row\Col
    A
    B
    C
    D
    E
    F
    1
    date item value Items Total
    2
    1-Sep
    A
    1
    A
    7
    3
    2-Sep
    C
    2
    B
    3
    4
    3-Sep
    D
    1
    C
    8
    5
    4-Sep
    A
    2
    D
    2
    6
    1-Sep
    B
    3
    E
    4
    7
    2-Sep
    E
    4
    8
    3-Sep
    C
    3
    9
    4-Sep
    D
    1
    10
    1-Sep
    C
    2
    11
    2-Sep
    A
    2
    12
    3-Sep
    C
    1
    13
    4-Sep
    A
    2

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,979
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    date item value Total Values Based on Item
    01-Sep A 1 A 7
    02-Sep C 2 B 3
    03-Sep D 1 C 8
    04-Sep A 2 D 2
    01-Sep B 3 E 4
    02-Sep E 4
    03-Sep C 3
    04-Sep D 1
    01-Sep C 2
    02-Sep A 2 the 7 next to a comes from
    03-Sep C 1
    04-Sep A 2 =SUMPRODUCT(($B$2:$B$13=E2)*($C$2:$C$13))

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,979
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    date item value 01-Sep 02-Sep 03-Sep 04-Sep
    01-Sep A 1 A 3 4 7 9
    02-Sep C 2 B 3 0 0 5
    03-Sep D 1 C 8 8 7 2
    04-Sep A 2 D 0 2 2 3
    01-Sep B 3 E 5 4 0 0
    02-Sep E 4 72
    03-Sep C 3
    04-Sep D 1
    01-Sep C 2
    02-Sep A 2 the 7 next to a comes from
    03-Sep C 1
    04-Sep A 2 =SUMPRODUCT(($B$2:$B$13=E2)*($C$2:$C$13))
    01-Sep C 2
    02-Sep D 1
    03-Sep A 2 then I added an extra term to allow you to analyse by date
    04-Sep B 3
    01-Sep E 4
    02-Sep C 3 so the 3 under 01-Sep comes from
    03-Sep D 1
    04-Sep C 2
    01-Sep A 2 =SUMPRODUCT(($B$2:$B$37=$E2)*($C$2:$C$37)*($A$2:$A$37=F$1))
    02-Sep C 1
    03-Sep A 1
    04-Sep D 2
    01-Sep C 1
    02-Sep A 2
    03-Sep C 3
    04-Sep A 4
    01-Sep C 3
    02-Sep D 1
    03-Sep A 2
    04-Sep B 2
    01-Sep E 1
    02-Sep C 2
    03-Sep A 2
    04-Sep A 1
    72

  5. #5
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,636
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    Gryrok,

    How about something like this?

    Excel 2007
    ABCDE
    1dateitemvalueItemTotal Values Based on Item
    21-SepA1A7
    32-SepC2B3
    43-SepD1C8
    54-SepA2D2
    61-SepB3E4
    72-SepE4
    83-SepC3
    94-SepD1
    101-SepC2
    112-SepA2
    123-SepC1
    134-SepA2
    14

    Sheet1



    Worksheet Formulas
    CellFormula
    E2=SUMIF($B$2:$B$13,D2,$C$2:$C$13)




    The formula in cell E2, copied down:

    =SUMIF($B$2:$B$13,D2,$C$2:$C$13)
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  6. #6
    Board Regular Weazel's Avatar
    Join Date
    Dec 2011
    Location
    Florida
    Posts
    3,155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    post 2 would work just fine

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,636
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    if we were totally an amount spend on an event, we could say "$5" "Promotions" and "$10" "Services" etc. etc., and in the table next to that, it would, in a single cell, add the total value assigned each label, like it shows below.
    It looks like we all have missed the above.

    Gryrok,

    Can we see what other $ values, and, text, that go with the other etc.'s?
    Last edited by hiker95; Sep 20th, 2016 at 12:01 PM.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  8. #8
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,979
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    the OP needs to clarify - his post 1 is badly written and has typos - OP ?

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,636
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    Gryrok,

    See reply #2 at the next link, to show an actual screenshot of the raw data, and, what the results should look like.

    http://www.mrexcel.com/forum/about-b...ml#post2507729
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  10. #10
    New Member
    Join Date
    Feb 2016
    Location
    Washington, DC
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I get a Sum From All Values in a column that have a specific value in the column next to them?

    Oh my goodness! Thank you all so very much for a nice simple clean solution. I figured there had to be a function somewhere, and I just didn't know which it was, and this was IT EXACTLY! Thanks again!

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
  •