Hi, I'm looking for a way to SUM data from a range of cells in the column above.
However, I want to only sum cells from part of the column.
e.g.
SUMIF(A3:A5,H1=5) ..... but SUMIF(A2:A5,H1=6)
In other words, if I change H1 for the numbers 1-6 (Years in Primary school) can I sum different cells in the A column
I tried attaching the workbook with a different computer - still no joy. The attachment button is there, but simply won't allow me to attach anything!
Last edited by jjmmoo; 09-20-2017 at 02:48 AM. Reason: Attachment
maybe try using SUM and OFFSET?
=SUM(OFFSET(A3,,,H1,1))
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
I can't seem to get that to work! Any other suggestions?
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
I changed H1 to C3, to keep my sample small...
B3=SUM(OFFSET(A3,,,C3,1))
Note the 3 commas in there
What was not working for you? Wrong answer? no answer?, something else?
I can't seem to attach my workbook, each time I press the 'attach file' button nothing happens!
I can't even attach a picture!
I'll try to explain again:
I have a workbook which is adding up the different levels of children in a class.
The levels go
1c, 1b, 1a, 2c, 2b, 2a etc. to 6a. (They run down the A column)
I then have the number of pupils on each level in the B column, e.g. 5 on 2c, 10 on 2b etc.
This data is being taken from a larger spreadsheet which has the data of the pupils in the school.
I want to add up the amount of pupils who are on target, In year 5 that means they need to be above 3b. So, when H1 says "5", I need the spreadsheet to add up all the levels from 3b-6a.
When H1 is changed to say "6", I want it to add up all the cells from 4c-6a (because the year 6 target is higher than the year 5 target.)
I'm sorry for not being able to attach, I'd really appreciate the help with this.
Hi - I can make that work, but when I change H1 to "6", I can't seem to make the offset change
When your C3 is 5 what adds up? If you change C3 to 6 what changes?
To attach an Excel file to your post,
- click Go Advanced,
- scroll down until you see Manage Attachments,
- click that and select Browse,
- select your file and click Open,
- click Upload and you will see your attachment below Upload Files from a website
- click Close this window,
- click Submit reply
After that you should see attachment in your post
When C3 = 5, the answer is 15 (1+2+3+4+5)Originally Posted by jjmmoo
When I change C3 to 6, the answer is 21 (1+2+3+4+5+6)
Do you by chance have calcs set to manual?
If We target 5 year then how you will say it should be 3B-6a, can you please explain the logic behind it and second you said if you will put H1=6 the cells from 4c-6a...?
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
The attachment should be on now - thanks.
Jjmmo can you please let us know how are you placing conditional formatting I mean how the numbers are starting and ending ? For dynamic formula you must clear the concept of these numbers .
I don't really understand what you mean.
The numbers in the cells are just random on the workbook attached.
On the real workbook, the numbers come through from a sheet where the teachers have 'levelled' the children in different subjects. This sheet is then counting how many children are on level 3c etc.
How did you make conditional formatting what is reason for making it???
....The numbers in the cells are just random on the workbook attached. On the real workbook...
You will get better, more accurate help when your sample data is more realistic. However, use the CHOOSE function to choose the sum range, thus:
blue;
Note that you only provided ranges for years 5 and 6.
Please Login or Register to view this content.
CF (blue):
over range: $A$6:$K$21Please Login or Register to view this content.
Last edited by protonLeah; 09-20-2017 at 03:32 PM. Reason: attachment
Ben Van Johnson
You're onto it I'm sure!
I've reattached. Hopefully you can see that I'm picking up data from one worksheet, but on this analysis page I want to be able to simply change the year group to see how many pupils in each year group are on a particular level.
Please can you try to explain the (SUM(CHOOSE... formula, I am struggling to understand it. If I could understand it, I'd have a go myself
More dynamic try
B22Formula:
Please Login or Register to view this content.
Copy across.
Still don't understand. Please can you download the workbook and then reattach?
Ok check the attached file
Do I need to keep the conditional formatting in place for this to keep working?
I'd like to change the conditional formats to match the cells highlighted on the right hand side of the spreadsheet (05:Q13)
No, there is no need for conditional formatting for working the formula.
Second if you want to make conditional formatting use =$O6=$H$1 formula in condition formatting rule by selecting the O6:Q13 range.
For the formula:
=SUM(CHOOSE(H1,,,,,B17:B21,B19,B21))
You have number in H1: 1 - 6, that will be used as the INDEX number for the Choose function.
Following the index number are a list of values to be selected by the index number. Since, in your original example, you only had 5 & 6, I put the sum ranges for fifth and sixth grades in the fifth and sixth value positions (notice the commas because there are no values for the first 4 positions). So that either B17:B21 or B19:B21 is returned to the Sum function (i.e., SUM(B17:B21) or SUM(B19:B21).
Last edited by protonLeah; 09-21-2017 at 04:24 PM.
Try:
Expected (green)
+1 Level (blue):Please Login or Register to view this content.
For conditional format, maybe:Please Login or Register to view this content.
level 1:
Expected:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by protonLeah; 09-21-2017 at 04:23 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks