+ Reply to Thread
Results 1 to 4 of 4

Sum Across Specified Columns with Specific Conditions

  1. #1
    Forum Contributor
    Join Date
    10-09-2015
    Location
    Canada
    MS-Off Ver
    Office Home and Student 2021 for Mac
    Posts
    170

    Sum Across Specified Columns with Specific Conditions

    Hi all,

    I'm having some trouble working through a formula that sums across specified cells with certain conditions.

    For example, I have a table of fractions and would like to add everything on either side of the "/" into a total column.

    So I have an series of alternating "M" and "F" variables and would like to total the "M"s as and "F" into a "Total M" and "Total F" column.

    I have the beginning stages of a formula, but would need to modify it slightly as it's not working quite as accurately.

    I've attached a workbook for (hopefully) further clarity.

    Thank you in advance!

    Darko
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Sum Across Specified Columns with Specific Conditions

    Wouldn't it be easier to enter the values as numbers instead of text, and then format them as fractions, if they are not a full number? Then you can just run a Sumif across the row to sum either M or F values.

    At the moment you are totalling 3/3 + 4/3 = 7/3 which is mathematically totally wrong. Unless you're not really trying to add up fractions. In your desired result, row 13, the values for M are 3/3,4/3, 1/3, 2/3 and adding them up will result in 3 1/3. Your desired result is 10/12, so not even 1. Can you explain the logic that leads to that result?

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Sum Across Specified Columns with Specific Conditions

    If these are not really fractions, and you really want to add up the numbers left and right of the / sign, then you can use this formula in cell N3

    =SUMPRODUCT(($B$2:$M$2=R$2)*(LEFT($B3:$M3,FIND("/",$B3:$M3)-1)+0))&"/"&SUMPRODUCT(($B$2:$M$2=R$2)*(MID($B3:$M3,FIND("/",$B3:$M3)+1,99)+0))

    copy across to O3.

    And in P3 you can use

    =LEFT(N3,FIND("/",N3)-1)+LEFT(O3,FIND("/",O3)-1)&"/"&MID(N3,FIND("/",N3)+1,99)+MID(O3,FIND("/",O3)+1,99)

    Copy all down.

  4. #4
    Forum Contributor
    Join Date
    10-09-2015
    Location
    Canada
    MS-Off Ver
    Office Home and Student 2021 for Mac
    Posts
    170

    Re: Sum Across Specified Columns with Specific Conditions

    Thanks teylyn, you're absolutely right.

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Convert rows to columns with the specific conditions
    By soleimani1967 in forum Excel General
    Replies: 2
    Last Post: 12-03-2017, 03:39 PM
  2. Replies: 8
    Last Post: 09-20-2017, 02:09 AM
  3. [SOLVED] Advanced Filter Between Two Columns all OR conditions (4 conditions)
    By nobodyukno in forum Excel General
    Replies: 2
    Last Post: 02-16-2017, 01:13 PM
  4. [SOLVED] Import specific columns from workbook A sheet1 to workbook B Sheet 2 to specific columns
    By Vassen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-14-2016, 07:01 AM
  5. [SOLVED] Attribute a specific number to a specific cell if conditions are fulfilled
    By joel.mugabe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2016, 10:16 AM
  6. [SOLVED] Copy specific columns to specific columns in another worksheet
    By mahat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2014, 11:24 AM
  7. Replies: 8
    Last Post: 04-04-2013, 08:02 PM

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