+ Reply to Thread
Results 1 to 5 of 5

help with conditional sum function

  1. #1
    Registered User
    Join Date
    10-24-2013
    Location
    sdsada
    MS-Off Ver
    Excel 2007
    Posts
    9

    help with conditional sum function

    Hello guys,

    i am searching a formula similar to sumifs but i need it for the following:

    i have some columns that are not one after each other and i want to sum the values in each row only from the cells in these particular columns and only if cell other column is BLANK...
    For example:
    i have columns S, AE, AQ, BC and BO. I want to sum the row 5 only if columns U(condition for column S), AG(condition for column AE), AS(condition for column AQ), BE(condition in column BC) and BQ(condition for column BO) are BLANK.

    In other words I need sum of column S(only if column U is blank)+column AE(only if column AG is blank)+ etc....

    Thanks in advance!

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: help with conditional sum function

    =sumif(U:U,"=",S:S)+sumif(AG:AG,"=",AE:AE)+sumif(AS:AS,"=",AQ:AQ)+sumif(BE:BE,"=",BC:BC)+sumif(BQ:BQ,"=",BO:BO)

    ?

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: help with conditional sum function

    hi dragonsnb. try:
    =SUMPRODUCT((MOD(COLUMN(S5:BO5)-COLUMN(S5)+1,12)=1)*(U5:BQ5="")*S5:BO5)

    or if those cells in between contains text, then this array formula:
    =SUM((MOD(COLUMN(S5:BO5)-COLUMN(S5)+1,12)=1)*(U5:BQ5="")*IF(ISNUMBER(S5:BO5),S5:BO5))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    if it doesn't work, I suggest you upload an Excel file in the thread. To do that, Click on GO ADVANCED and use the paperclip icon to open the upload window.
    View Pic
    and to illustrate your example better, click on How To Get Quick & Good Answers

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: help with conditional sum function

    =sum(if(U1="",S1),if(AG1="",AE1),...etc...)

  5. #5
    Registered User
    Join Date
    10-24-2013
    Location
    sdsada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: help with conditional sum function

    Quote Originally Posted by yudlugar View Post
    =sumif(U:U,"=",S:S)+sumif(AG:AG,"=",AE:AE)+sumif(AS:AS,"=",AQ:AQ)+sumif(BE:BE,"=",BC:BC)+sumif(BQ:BQ,"=",BO:BO)

    ?
    thanks this works perfect for me formula goes a little long but works !

+ 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. Conditional Function Help
    By oyz79 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-04-2013, 01:23 AM
  2. Help on Conditional Min Function
    By Spfbabe in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-12-2012, 07:45 AM
  3. conditional sum function
    By luegofuego in forum Excel General
    Replies: 6
    Last Post: 10-21-2010, 03:06 AM
  4. Conditional Min function
    By mitelkm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2008, 12:23 PM
  5. If Then Conditional Function
    By spiderman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2007, 04:03 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