Amendment to Sum Formula

**pauldaddyadams** · 08-30-2012, 12:37 PM

Hi Guys

My orignal problem was posted and resolved here by NBVC:

HTML Code:

However there is a new requirement which I need some help with. The only way I know how to do this will involve rearranging all the data I have already which I want to try and avoid if possible?

Currently on the “Dashboard” sheet the user can select the following:

All (Sums all the 6 branches below)
Branch 1
Branch 2
Branch 3
Branch 4
Branch 5
Branch 6

I now require a similar feature to "all" where if the option box 1&2 is selected it sums branches 1 and 2 and if option box 3,4,5 is selected it sums the three branches.

Can this formula be adapted in anyway?

Please Login or Register  to view this content.

One other problem I have is when I add these option boxes and link them to the same cell as the others it doesn’t treat them as the same group – why is this?

Can anyone help?

**pauldaddyadams** · 08-30-2012, 04:34 PM

Can anyone help with either problem (formula or option box)?

**NBVC** · 08-30-2012, 04:39 PM

In order for it to function properly, you need to have the options all organized under one group. Then these 2 new options translate to 8 and 9 in C4 of the Calcs sheet (you'll need to do the design work, but keep them inside the group outline), then you can use formula:

Please Login or Register  to view this content.

**NBVC** · 08-30-2012, 04:53 PM

pauldaddyadams,

I mucked up the formula a little. I just noticed... the above formula and attachment should now be correct. Please see those.

**pauldaddyadams** · 08-31-2012, 05:08 AM

Thanks NBVC once again for the formulas.

Can I ask when these mean:
IF($C$4=8,"<="&$B$4&" 2",
IF($C$4=9,">="&$B$4&" 3",
IF($C$4=1,$B$4&"*",$B$4&" "&$C$4-1)

e.g the first IF above will be adding branch 1 and 2 together but I cant see how excel would know this.

the same with IF($C$4=9,">="&$B$4&" 3" - how does it know to add 4,5, and 6? If I wanted to change this to only add 4 and 5 how would this be written?

**Ace_XL** · 08-31-2012, 07:19 AM

Please Login or Register  to view this content.

These sub-formulas are a component of the simfs formula and define your second criteria.
Simply put, these equate to
If(c4=8,"<=Branch 2"), hence it would sum values for "Branch 1" and "Branch 2"
If(c4=9,">=Branch 3"), hence it would sum values for "Branch 3","Branch 4", "Branch 5" and "Branch 6"
IF(c4=1,"Branch*") implying it to sum values for all Branches

**pauldaddyadams** · 08-31-2012, 07:25 AM

I still don’t fully understand this formula to be able to calculate this myself...

I was speculating previously but I do require an amendment to selection 9 - previously I needed branches 3,4,5 added together but the formula would also include branch 6 which is incorrect. Is there a way to exclude branch 6? in fact my requirement is for it to sum branches 3 and 4 together so the IF formula would need to be greater than 2 but less than 5 - is that right?

Jumping the gun a bit if I wanted to add branch 1 and say branch 6 - is this possible due to the way the data is laid out?

**pauldaddyadams** · 08-31-2012, 07:40 AM

thanks Ace_XL - I thought it must be that just wanted conformation.

So you know how to write the IF to exclude branch 6? or say Branch 1 and Brach 6?

**Ace_XL** · 08-31-2012, 07:50 AM

I cannot open the attachment.. but this example would perhaps give you a lead into summing your selections

=SUM(SUMIFS(C1:C18,A1:A18,"criteria1",B1:B18,{"Branch 1","Branch 3"}))

**NBVC** · 08-31-2012, 08:53 AM

Hi Pauldaddyadams, I apologize for not noticing that there was a exclusion of Branch 6 in the grouped branch options...

As Ace_XL eluded to you can wrap the SUMIFS in a SUM or SUMPRODUCT function to allow for multiple choices as he has shown....

so this is my version of the new formula:

Please Login or Register  to view this content.

So in this formula if your choice was group 1/2 then cell C4 changes to 8 as this is the 8th option you added, and so then the formula essentially does 2 separate SUMIFS for branches 1 and 2, and adds them sums then together with the SUMPRODUCT.... if you pick group 3/4/5, then C4 becomes 9 and so now you have essentially 3 separate SUMIFS and added together. If you choose ALL, then C4 is 1, and the wildcard * indicates to sum all Branches together... any other choice is a specific branch choice and since the Branch 1 option is the 2nd option, it returns a 2 to C4, so we need to subtract 1 to offset it back to indicate you want Branch 1,.. and so on.

I hope this helps you understand the concepts in the formula.

The fact that you are indirectly refencing the other sheets, makes the formula look more complex with the INDIRECT function needed, and having to line up columns is needing the INDEX/MATCH functions...

**pauldaddyadams** · 08-31-2012, 10:42 AM

Excellent - Thanks so much for this! the formula was brilliant

I now fully understand the concept! The Sumproduct is a very powerful formula - I will use this more in the future i am sure!

Amendment to Sum Formula

LinkBack

Thread Tools

Rate This Thread

Display

Amendment to Sum Formula

Re: Amendment to Sum Formula

Re: Amendment to Sum Formula

Re: Amendment to Sum Formula

Re: Amendment to Sum Formula

Re: Amendment to Sum Formula

Re: Amendment to Sum Formula

Re: Amendment to Sum Formula

Re: Amendment to Sum Formula

Re: Amendment to Sum Formula

Re: Amendment to Sum Formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions