+ Reply to Thread
Results 1 to 4 of 4

Need to sum multiple locations for 1 program, current results yield 0

  1. #1
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Orlando, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    253

    Need to sum multiple locations for 1 program, current results yield 0

    Okay, I need to sum based on multiple criteria. I have 2 other functional formulas, but I just found out for bonus calculations that I need to include another criteria.

    =IF(C178="-","-",(SUMIFS(tma_ToursQual,tma_ToursLocation,$C178,tma_ToursMktgProg,info!$AI$2)))

    C178 equal a location number, in this case location #4401
    info!$AI$2 = the marketing program, which happens to be 12

    I have this formula, for each of the 4 tour locations (like 4401). The others are 4402, 4412, 63, and 64. I have a named range for this table of tour locations called CP_TL2_Locations

    My other formula is:

    =SUMPRODUCT(--(tma_ToursLocation=$C178),(tma_ToursQual))+SUMPRODUCT(--(tma_ToursLocation=$C179),(tma_ToursQual))+SUMPRODUCT(--(tma_ToursLocation=$C180),(tma_ToursQual))+SUMPRODUCT(--(tma_ToursLocation=$C181),(tma_ToursQual))+SUMPRODUCT(--(tma_ToursLocation=$C182),(tma_ToursQual))

    This formula above sums the 5 locations, but for both marketing programs (there are 2 marketing programs: 12 and 67). Either way, I need to modify one of the 2 formulas to caputure all 5 locations (there are 12 on my data table), but only for the marketing program 12. I have made multiple attempts, however when I am not getting an error that prevents me from saving the formula, I end up with a result of 0. Any ideas?
    ~*~ Sherry ~*~
    Orlando, FL

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need to sum multiple locations for 1 program, current results yield 0

    Hi

    I think you can understand that is too difficult to suggest you, anything, as we can not see your data.

    Would you pls, upload a sample workbook?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Orlando, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    253

    Re: Need to sum multiple locations for 1 program, current results yield 0

    Fotis---

    I understand, as I have been racking my brain on this one. I decided to create an additional table to make the additional calculations, based on my initial formula below. The new table calculates each of the 4 locations (currently: $C178) seperately, and then I use a simply sum formula. This way it gets me what I need, and I can also use this table to validate my data against the itemized source data. I certainly appreciate your attempt. I have found through my years of reading this forum, that often times it is easier to create an additional table then have "supersized" formulas (like my 2nd formula) bogging down the spreadsheet!

    =IF(C178="-","-",(SUMIFS(tma_ToursQual,tma_ToursLocation,$C178,tma_ToursMktgProg,info!$AI$2)))

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need to sum multiple locations for 1 program, current results yield 0

    It's nice that you found your solution.

+ 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