Hi all
Is it possible to find the result of a sum of numbers in some other column. I am attaching a workbook example.
Thank you.
Hi all
Is it possible to find the result of a sum of numbers in some other column. I am attaching a workbook example.
Thank you.
Last edited by petar256; 02-05-2018 at 10:28 AM.
Maybe this?
Formula:=SUMPRODUCT((F3:F8<>"")*(C3:C8))
OR
Formula:=SUMIFS(C:C,F:F,"<>")
Last edited by AlKey; 02-05-2018 at 10:04 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I have a feeling this is one of those "Sum of numbers equal target value" posts.
@petar256 can you give us an example of what the expected result is? Its unclear what it is you are trying to accomplish.
Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.
"I am here to help, not do it for people" -Me
Hi
I will try to rephrase;
I have to find the amount of each cell in column B is it somewhere in column A?
So if there is a sum of x numbers in column A that gives a result that is in column B i want to do something.
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.
Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Ah ok, so I was right its "Find x values who's sum is y".
This is a very simple question but is mathematically very complex. While Excel can do it, it very quickly gets to a point that its not feasible to use Excel. The complexity is also effected by any constraints on the solution.
So for example: Are we looking for the first combination (sum) that matches the target value? Do we care how we find a combination of values whos sum is equal to target (largest values = target, fewest values = target, etc)?
Some constraints mean we get to stop looking sooner, other constraints mean we need to exhaust checking all possible combinations.
I presume also that for your purposes we can only use a value in Column A 1 time? IE: we cannot do 100+100+100 (being that 100 is in the column only 1 time)?
Taking the example you gave, with 6 values in A and 3 in F, the worst case scenario (needing to check all possible combinations) is roughly:
40,000 possible combinations per value in F or 120,000 total combinations (presuming we arent checking for all 3 target values at the same time which could potentially drop us down to 40,000 ish).
The number of x possible combinations very quickly gets out of hand with the more values you have and I am presuming your actual file has more values than your sample.
I was kind of expecting this..Yea..original file has over 10000 rows with diferent values...
think im gonna have to find different solution.
Thanks anyway
Yea 10,000 rows would likely take a lifetime. I saw 30 values still going after 24 hours before (maybe with some conditions I dont recall).
I dont know how, as I dont do Access, but I know that there is a way it can do this very fast compared to Excel (as we ended up using it for a customer instead of Excel in the past). Might be worth researching.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks