I just made up this sample book to see if we together could solve this easy puzzle.
I got 2 different collums (Count and Size). I'm using Countif to look for my value, and it does find them and show how many of that size there is, but I also want it to depend on the count value, and multiply the certain size with the specific count.
For example
See attached workbook
sample book.xlsx
My first thought is that you will probably have to resort to VBA (macro)
I'll see if I can come up with a formula but those are not my strong
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
I could use a macro (VBA) as well, doesn't have to be a formula. Thanks Keebellah.Originally Posted by Keebellah
With a macro it's quite simple
Do you know how to write a macro?
You use the same range and check for the size value and the just add the number in the offset (0,-1) as count.
I'm not to familiar how to write the vba macros, I'm just good at understanding them once they are written xD
So if you could give me a hand I'd appreciate it alot.
If you are still interested in a formula solution try this in C32 and fill down.
(I come up with a different value for Count 660 ... 33)Formula:
Please Login or Register to view this content.
In order to get the same counts you expect I used this formula.Formula:Please Login or Register to view this content.
Edit Please see my next post.
Last edited by FlameRetired; 07-11-2018 at 05:16 PM. Reason: After thought
Dave
The formula works great
I wrote the following function
It's also in the attached file as well as the formula
Please Login or Register to view this content.
Pfft! I way over complicated it. Try this instead.Formula:Please Login or Register to view this content.
@FlareRetired: your formula is correct but the OP wants to add the values next to the size values so if there is a 1 to the left of 660 it counts one is, if there is a 2 then that one counts for 2 etc.
The vba Function I wrote takes that into consideration.
Yes and so did my first formula, but the results in the upload suggest they want a count of the counts ... ie 1 counts as 1 and so does 2. We've both figured sum of counts which is how I interpreted the instructions at first.
Try this in your workbook in C32.Formula:Please Login or Register to view this content.
Here they are:
What OP's upload shows is:
I think we need feedback from OP.
Last edited by FlameRetired; 07-11-2018 at 07:20 PM.
True, the OP is the only one to clarify this, but the OP first upload was that he knew how to count the size occurrences and that he/she was looking for a way to add the values next to the sizes, so I think ....
Well, we wait
We did our best
I want to thank you both for the effort you put into my question. Both of the formulas/functions works great, So I'll rep you both!
As you describe my case it's accurate. My formula only counted the value of the 660 for ex. and didn't calculate the ammount of them, but yours does!
Thanks again, this will help me alot in the future.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
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.
Ah, forgot. Marked it as solved now
Last edited by AliGW; 07-12-2018 at 06:01 AM.
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks