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

1. ## 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?

2. ## 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?

3. ## 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. ## Re: Need to sum multiple locations for 1 program, current results yield 0

It's nice that you found your solution.

#### Thread Information

##### Users Browsing this Thread

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

#### 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