• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with a count? I think it should be easy!

Davealot

Davealot

Member
Greetings,
I'm sure this is an easy fix, I just have trouble with the loop in the function. Looking to write up code to check a range, B:B, for a specific set of text (MID,1,9)="76200-tk8", if it meets that criteria to pull information from adjacent column for quantity, and to loop through until it no longer matches the text, so that I can drop an open order report full of part numbers to sum up the totals in one cell.
Column B would be the part numbers, Column C would be the open amount, and I would have a separate sheet that would count the total amount of qty, matching criteria in a single cell.

I can obviously do a "CountIf" function, then sum that up in a separate column, but why do that when I know code can do it? I just can't get the loop down pat yet. Any help?
 
My bad, Forgot to attach. Needing it to search part numbers for criteria, find each part number and sum the total based on criteria. I was thinking I would need to use a sum if function, but the problem is that we have so many part numbers that are slightly varying in part number by models that I'm afraid a sumif won't function properly, thanks
 

Attachments

  • Sampleload.xlsx
    10.8 KB · Views: 4
If I add up all the Balances where the adjacent Part No's that start with "76200-tk8" I get a total of 1784 which are in 44 records

can you please explain the logic better
 
If you add a field to the Table "Left 9"
You could setup a Pivot Table
upload_2016-4-11_23-19-51.png
 
I'll admit I never even considered a pivot table, I was thinking something along the lines of writing code that would loop through each part number and if criteria was met, "76200-tk8" for example it would spit out the sum at the end. The pivot table will work well, but I get a new report every Monday, and I'm trying to develop something that will keep me as hands off as possible
 

Hi !

Use a filter and SUBTOTAL worksheet function for example …
Or better a COUNTIF as you well wrote …
What is your difficulty ?
​
 
Here's my solution. Any of the above provided work too lol. I'm just replying cos I asked for sample file
 

Attachments

  • Sampleload.xlsx
    13.8 KB · Views: 6
You must log in or register to reply here.
Back
Top