# Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

1. ## Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

I have a massive spread sheet, single tab, but thousands of lines.
Multiple customers, various part numbers, and quantities.
Attached is a very consolidated example.

I'm trying to zero in on a specific part number, (366102) it will always be that specific part number, take all the quantities for that specified part number, sum them, then multiply them by 25, and that is my Output.
So in the example sheet, I would expect the output, with nothing filtered, to be 1200.
That's the base of the formula, but I want the output to change based on the filtered customer.
So in my example spreadsheet, if I filter by customer Foxtrot, I would expect my output to change from 1200 to 400.
I hope that makes sense.
I really appreciate the help.

2. ## Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

Is it SUMIF(S)?

=SUMIF(B2:B16,366102,C2:C16)*25

=SUMIFS(C2:C16,A2:A16,"Foxtrot",B2:B16,366102)*25

3. ## Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

In cell E1, try:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C16)-ROW(C2),))*(B2:B16=366102))*25

As you filter the customer, the total will change.

4. ## Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

Originally Posted by Gregb11
In cell E1, try:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C16)-ROW(C2),))*(B2:B16=366102))*25

As you filter the customer, the total will change.
THIS IS IT!!!!

However...I'm having an issue that I've had on other spreadsheets and I'm not sure how to fix it.
This formula will be in a "template" with no data.
When I get the data in a report weekly...I copy from the report and paste values in the template.
When I do this...the cell with our formula stays at 0.
But if I go down the column of part numbers, double click in each cell and hit enter, they start to tabulate in our formula cell.
Or if I manually type in all the part number...works perfect.
But when the report is over 3000 lines...that's not feasible.
I've had this before and I've yet to understand why, I've checked the formatting of the cells, it's maddening.
Do you happen to know how to resolve that?

5. ## Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

I'm not sure. Can you attach the file?

6. ## Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

Never mind...I figured out how to make it work.
I have to use Paste Option: Match Destination Formatting.

Thanks again...that was a big big help.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1