Macro to sum selected cells into a new row - size and positions ARE RELATIVE

Hi guys after great experience with the geniuses on this forum iam asking for more of your time and expertise to solve this little porblem… iam to newbie in excel to figure this out myself… if somebody find some time to look at this i would be very thankfull

Iam working on a bit advanced food diary… ill show you on pictures what i would need if anyone have a bit of time to help me please…

check the attached diagrams and the sample excel (had to use xlsx or some functionality error pops-up)...

As you can see from diagrams i need quite a simple macro that will take a manually selected region (which will be sometimes 1 line, sometimes 2 or more so it has to be flexible) and SUM the columns of the selected region into new row of cells which will be positioned relatively to the position of the source region…

The main trouble is the relative size of the source region (sometimes i will select 1 line sometimes more) and the generated row with SUM of values must be always positioned relatively to the currently used region - always relatively to the top line, right corner of the source region…

plaease check the pictures and maybe if you can… post a macro code that i can paste into macro editor and assign a simple shortcut that will trigger the macro AFTER i manually select the region i want to SUM

any help greatlly apreciated… thanks everyone

macro03.gifmacro02.gifmacro01.gifmacro_sample.xlsx

Maybe with an filter on the data.

I used subtotal (in the yellow cells).

See the attached file.

hmm thats pretty cool way to do it but i guess ill have to save it for other time because the way my real food diary spreadsheet is layed out (the thing posted here is just a cut down dummy) iam planing to do some more stuff with the macro-generated rows later and this solution although MUCH MORE ELOQUENT in a single worksheet scenario, it wouldnt work with my real food diary that is going to use the generated rows for some more advanced stats calculations (like weekly stats, percentage, etc. nerdy stuff)...

i feel bad you spent time trying to help me and i seem like i dont appreciate it... I DO and i guess i will use this filter function elsewhere in the food log very shortly because it just looks like a very practical and handy way to work with value sets... but for this particualar part of the diary it would complicate things in the long run... iam very sorry... thanks a lot

do you think its too complicated to create the macro with keyboard shortcut as i envisioned ? like:

1) select region of x lines
2) hit keyboard shortcut for macro
3) generate a SUM row that is positioned relatively to the top-right corner of the source selection region

iam sorry iam so stuborn but i would have to recreate too much other stuff that i already painfully managed to get working to fit different solution and iam too new to excel so i would have to ask too much help to tailor my real spreadsheet to different scenario that the above 3 step macro

i hope i dont sound like ungratefull jerk...

Please Login or Register  to view this content.

Not at all.

Non VBA solution.

I made an new format with your data.

After that I made an pivot table of it.

See the attached file.

The pivot table is on a seperated sheet.

Please reply.

Non VBA solution using original format from post #1...

Put this formula in K6, then copy down and across through Q20:

=IF(AND(C6>0, C5=0), SUM(C6:INDEX(C6:C$20,MATCH(0,$D6:$D$20,0))), "")

As long as you leave a blank row between meals, the totals for each section will appear automatically.

JBeaucaire> your solution works PERFECT for the SUM row just i would love to add 1 more after-thought if you would be so kind, once again...

this is the original "magic" formula that generates the SUM row with 7 values (grams, protein, fat, carb, fiber, sugar, cal):

=IF(AND(C6>0, C5=0), SUM(C6:INDEX(C6:C$20,MATCH(0,$D6:$D$20,0))), "")

here is my next step id love to acomplish using the same magic you came up before, if you could help me with this one too...

NEW EXCEL SAMPLE
percentages for each line.xlsx

now in addition to the previous 7 values genrated i wish i could have similar formula to spread across last 3 columns that would calculate % of calories for each macronutirent within the total sum of calories for that food

i was able to succesfully calculate the % of macronutrients form total Cal on the TOP SUMMARY line because it doesnt move and is static but i cant do it on your live generated lines with your code because i just dont understand nothing how your magic formula works even if id try for several hours to decipher what each operator means... :/

as you can see in the TOP SUMMARY the 3 new values are simple percentage division

it simply takes CAL for that food line and

1) firsts converts grams of protein, fat, carb into Cal
2) finds % of each macronutrient Cal from the total Cal for that line

you can see in the TOP SUMMARY line (i use dumb reversed percentage equations):

Total Cal / (protein grams * 4)
- there is 4 Cal in every gram of dietary protein
- i simpy used =SUM((L4*4)/(Q4/100))

Total Cal / (fat grams * 9)
- there is 9 Cal in every gram of dietary fat
- i simply used =SUM((M4*9)/(Q4/100))

Total Cal / (carb grams * 4)
- there is 4 Cal in every gram of dietary carb
- i simply used =SUM((N4*4)/(Q4/100))

The fiber value and some other trace minerals skew the % a little bit so sometimes when you add % protein, % fat, % carb its not 100% but 98% or 92% in case there is a lot of fiber... but its ok as long as the % for main 3 macronutients is calculated from the total Cal for that line

I know this is 3rd time iam asking for help in last week but I would be very glad if you could help me with this last one and i hope its done... 100% your code... SO nice and simple...

Thanks a milion

R2: 4
S2: 9
T2: 4

R6: =IF(ISNUMBER(L6), SUM((L6*R$2)/($Q6/100)), "")

Copy R6 down and across.