Formula to count repair parts based on multiple row critierias

[sgderrick]

Dear Experts,

I need some help to do some counting. I have a database which contains repair order lines. I need to count the following,

1. Number of repairs done using "Kit"
2. Number of repairs done using "Singles"
3. Number of repairs done using "Kit" + "Singles"

The fields available

1. Sales Order : 1 sales order can contain number units sent in for repair (multiple line number)
2. Line Number: Line number indicates the number of units sent in for repair (#1 means this is the first unit, #2 means this is the 2nd unit etc.. )
3. Part Type : The classification of spare part type, either "Kit" or "Single"

Data organization :

1. Each row contains the spare part used in the repair (Sales Order can be duplicated if there are more than 1 unit in this order for repair) & Line Number can also be duplicated if there are more than 1 spare parts used for the same unit under repair.

As there are more than 20 thousands line per country, it is quite impossible to get it manually counted. Is there any formula that can help me automate the task of counting? (in a single worksheet? or copy into 3 different worksheets and do the manual filtering?)

Thanks,
Derrick

[etaf]

on your example
can you provide the answers you hoped to get from that data sheet

1. Number of repairs done using "Kit" = 3 is that correct number of times you see Kit - OR if a different answer why - what are the rules
2. Number of repairs done using "Singles" = 7 is that correct number of times you see single - OR if a different answer why - what are the rules
3. Number of repairs done using "Kit" + "Singles" and 10 again if different why

[bebo021999]

What is your expected results?

[sgderrick]

Hi etaf and bebo021999,

Sorry for not being clear enough, I had updated the file and use color code to classify the expected result.

In the same sales order with more than 1 line #, means it is more than 1 item for repair.
For example,
Sales order 1234, there are 3 lines(meaning 3 items for repair), row 1,2 and 3 belongs to item#1 (because all of the parts used belong to line #1)
row 4 belongs to item#2 and row 5 belongs to item#3.

Is this clear? Or am I making it worse?

[sourabhg98]

Can the line number be across various rows or will they be continuous. Like in the sample you have all the same line numbers of an order are together. for example first 2-4 rows have 1 line number. Can this be scattered? So can you have Line number #1 in row 2, then line number #2 in row 3 and then again #1 for the same order in row 4?

[sgderrick]

hi Sourabhg98,

Thanks for helping, they will not be scattered, the line number will be in sequential order if there are more than 1 items for repair in the same order number. If there is only 1 item inside the order #, it will be only line #1, follow by different rows of parts used (if more than 1 part are used). After that will be another order number and it will start with line #1 again.

Derrick

[sourabhg98]

Okay check attached. Check the "result" sheet for the results.
This was really a challenging problem.
I think I cracked it finally.
please note that 12 here represents total rows + 1,
In H2-

Please Login or Register  to view this content.

In I2-

Please Login or Register  to view this content.

Just replace 12 by number of rows in your data + 1 and copy down the formula.

[sourabhg98]

Making it more convenient to use.
Nothing to change in the formulas.
Just copy them down to the end of your data.

note- this would work for 100K rows of data.

[sgderrick]

Thanks sourabhg98, let me test and check it out. As the file have more than 20k lines of data, let me digure out how to test the integrity of the result to make sure all is ok as i have to duplicate the same counting to other countries.

[sgderrick]

hi sourabhg98, Yes the formula should work... I only checked a few hundred repair entries and the results are correct
I am wondering why do the result for kit + single appear on the first line # whereas the kit and single will appear on the last line #

Many thanks for your help (and others too).

[sourabhg98]

No there is no reason for that. I could have made them all show results in the last line. That's just coincidence.
But yes, that was the trick.
I had to make a formula so that it doesn't duplicates results, so now formula gives only results in either the first (kit+single) or the last line(single/kit).
I made all the three formulas separately and then clubbed them, that may be the reason I used the first line for kit+single but last line for kit/single. However, it wont affect the results anyhow.