Hi
Is it possible to sum the value from only the first five cells with contentin in each column (group) ?
Date Group 1 Group 2 Group 3 Group 4 Group 5
11.04.2018 12
12.04.2018 1
13.04.2018 2 4
14.04.2018 23 4 3
15.04.2018 34 6 3
16.04.2018 34 4 4
17.04.2018 3 15 6
18.04.2018 43 4 4 3
19.04.2018 21 4 2 2 1
20.04.2018 34 4 3 2 1
21.04.2018 3 4 2 32 4
22.04.2018 2 4 6 12 4
23.04.2018 2 6 5 12 4
24.04.2018 32 7 26 21 4
25.04.2018 12 7 2 21 6
26.04.2018 12 3 6 2 7
Regards,
Kjersti
Welcome to the forum.
Here are two methods. The first uses helper columns, the second uses an array formula.
Method 1
This uses helper columns. With your data to be summed in B2:F17, the helper columns will be in G2:K17.
1. In G2, enter this:
Formula:
Please Login or Register to view this content.
2. Drag it down to G17.
3. Drag across to K2:K17.
4. The last number in each helper column is the sum of the first five numbers in the original columns.
5. You can put this summed number elsewhere (under your original data, for example) using this, dragged across for five cells:
Formula:Please Login or Register to view this content.
Array formula
With your data to be summed in B2:F17, put this in column B under your data - let's say in B19:
Formula:Please Login or Register to view this content.
It's an array formula, so enter it using Ctrl-Shift-Enter not just Enter. You should then see curly brackets { } appear round the formula in the formula bar - do not try to enter them yourself. If you just press Enter you will get a #NUM! error. In that case, just click into the formula bar and try Ctrl-Shift-Enter again.
Drag across to under each of your Groups.
I hope one of those is useful.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Hi, to all!
Another option, without CSE and without volatile functions:
=SUMPRODUCT(N(MMULT(N(ROW(B2:F17)>=TRANSPOSE(MMULT(ROW(B2:F17),1))),--ISNUMBER(B2:F17))<=5),B2:F17)
Blessings!
Last edited by johnmpl; 01-13-2018 at 03:59 PM.
Just FYI, SP is still a volatile functionOriginally Posted by johnmpl
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
What? You mean Sumproduct is a Volatile Function? No way!!!Just FYI, SP is still a volatile function
Volatile Functions:
RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), INFO()
Blessings!
My apologies, I meant to say that Sp is still an ARRAY function.
I just Say that they fórmula is without CSE (without keys Ctrl+Shift+Enter... But by Nature are Array operations. Blrssings!
John - FYI, those functions are often quoted online as the most common volatile functions, not the only ones. A quick Google soon reveals that there is a lot online about the volatility of SUMPRODUCT.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Re: Is it possible to sum the value in the column from only the first five cells with cont
Thank you for your help!
johnmpl's proposal worked just the way i wanted. Great
Regards,
Kjersti
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks