Using IF or IFS or IF/AND or IF/OR will not give me the result I am looking for.

[jerminski73]

I have a document # in Column C, Product Type in D, Quantity in E
What I want is a multiple IF scenario. The scenario: Each load we haul has 3 product types available and if we haul all 3 types then each load will have 3 lines of data, if we haul 2 then it will generate 2 lines of data, if we haul 1 full load then it will only generate 1 line of data. Now for the rest of the fun, I have to combine 2 product types into 1 value and the 3rd item will be all on it's own. Item 65 & 124 need to become combined values, Item 99 will always be separate.

Problem 1: I created a way to combine flag the document number and tell me if the doc has multiple line items. (Row 1 will always be a "1" so that's a given). Right now the formula is set to if line 2 does not match line 1 then enter "1" if it matches line 1 then return value of "2". Right now the first line of the document number will always be "1" without any formatting, any additional lines with the same Doc# will be "2" formatted yellow cell. Is there a way to give me a return value of 3 for the third item?

Next problem after figuring out the above question: I have Product flagging in column H, Product 65&124 are combined values for a grand total, product 99 always has to be separate from these two product types. I Sort my Sheet by Doc then by Product Flag so it puts all the 1's together and then the trailing 2 at the bottom of the lines. Now I want to combine the values flagged as 1's by Doc#.

So,
Doc 1000542 has 3 products, Total product is 7500, I need the sheet to tell me product Flag 1 total equals 6500 and product Flag 2 equals 1000.
Doc 1000543 has 2 products, Total product is 7500, I need the sheet to tell me product Flag 1 total equals 7500.
Doc 1000544 has 1 product, Total product is 7300, I need the sheet to tell me product Flag 1 total equals 7300.
Doc 1000545 has 2 products, Total product is 7700, I need the sheet to tell me product Flag 1 total equals 7700.
Doc 1000546 has 1 product, Total product is 6900, I need the sheet to tell me product Flag 2 total equals 6900.
The way I have it setup is not terrible compared to the way it used to be but I am sure there is a better way!.. Thanks in advance for any assistance!

 

[AliGW]

Could you please attach a sample workbook (not a picture of it)?

 

[jerminski73]

Thanks for your interest in helping out Ali!

 

[CorrelateSpilled]

For problem #1, are you looking to have a running occurrence of Doc # in your list? If so, I think you can input this formula into F3 and then drag the formula down the rest of the rows in the Doc Flag column:

=IF(C3=C3,COUNTIF($C$3:C3,C3),"")

 

[AliGW]

You don't need the IF clause. This will be enough:

=COUNTIF($C$3:C3,C3)

I am struggling to understand what is right in the sample workbook and what is wrong, what needs fixing, etc.

A sample workbook is better when you MANUALLY enter the results you want and add annotations to explain the logic behind those results. Leaving in formulae that may or may not be doing what you want is (a) potentially misleading and (b) requiring of us to try to reverse engineer your thought processes.

 

[jerminski73]

=countif fixed the counting problem, thanks for that!
Notes: I Need a formula that will sum Product 62 &142 when both are on the same doc#. 167 Will always be its own qty even if it is paired with other products.
Raw data is in column E, The Qty to Enter is Column K, the descriptions I need help with are in L. K is my problem child, All other columns are good to go.

 

[CorrelateSpilled]

I think the following formula does what you are looking for:

=IF(OR(J3>1, J3=COUNTIFS($D$3:$D$14, D3, $M$3:$M$14, M3)), SUMIFS($E$3:E3, $D$3:D3, D3, $M$3:M3, M3), 0)

Insert that formula into cell K3 (Qty to Enter column) and then drag it down the rest of the rows in that column. In the above formula,

• J refers to the Doc Flag column
• D refers to the Document # column
• M refers to the Product Flag column
• E refers to the Gallons column

 

[jerminski73]

THANKS FOR THE REPLY, SOOOOO VERY CLOSE! When I inserted the formula into K3 as instructed it wall worked well except when the document is always longer than 14 rows (more like hundreds of rows every time lol) so I changed the $D$3:$D$14 and $M$3:$M$14 to $D$3:$D14 and $M$3:$M14. I believe the initial formula was calculating only rows 3:14 in their respective columns, is this correct? The nerd in me wants to know! Thank you sooooo much for your responses!

 

[CorrelateSpilled]

Yes, the initial formula was only calculating rows 3-14 because those rows were the first and last rows of your spreadsheet data. $D$3 and $M$3 in the COUNTIFS function should correspond to the first row of your table while $D$14 and $M$14 should correspond to the last row of your table. If you remove the $ in front of the 14, when you drag down the formula the 14 would increment by one for each subsequent row. This is because removing the $ in front of the 14 would change the cell reference from absolute to relative.

You can see that scenario happening within the SUMIFS function. There is no $ sign in front of some of the E3, D3, and M3s. And when you drag down the formula, those values become E4, D4, M4, in the next row, and E5, D5, M5, in the following row, etc.

I think if you turn your data into a table, then the $D$14 and $M$14 should automatically update when you add new rows to your table; and you wouldn't have to drag down the formula.

 

[jerminski73]

Using the same xls above, pasting your formula into K3 worked great with the corrections we spoke about here. I tried reverse engineering your formula to find the answer that I need below but only succeeded in breaking it more lol. I have literally today been tasked with another set of rules to figure out. Now on top of needing Qty by product type per Document I need Total Qty per Document as well.

Starting in K10 because there are 3 products on this particular delivery. Basically reading Column J as my flag for number of items on the same delivery,
If the flag in J8 is 1, total Gallons from E8 in L8 The end result would be 1000 using K8 thru K10 looking at the flag in J
If the flag in J8 is 2, total Gallons from E8:E9 in L9 The end result would be 7500 using K8 thru K10 looking at the flag in J
If the flag in J8 is 3, total Gallons from E8:E9 in L10 The end result would be 8000 using K8 thru K10 looking at the flag in J

Thanks again for your help!

 

[CorrelateSpilled]

Sounds like you want a running total quantity of Gallons per Document #. In that case, input this formula into the first row of data of your Total Qty column (cell L3) and drag it down to the last row with data.

=SUMIF($D$3:D3, D3, $E$3:E3)

 

[jerminski73]

Did not work for me, new sheet uploaded.

 

[CorrelateSpilled]

Sorry, I originally misunderstood what you were looking for. Now I understand you just want one total quantity for each Document #. The below formula should do what you are looking for. Input it into the first row of your Total Qty column and drag it to the last row with data. Here, $D$3 and $E$3 should refer to the first row with data while $D$14 and $E$14 should refer to the last row with data.

=IF(J3=COUNTIF($D$3:$D$14, D3), SUMIF($D$3:$D$14, D3, $E$3:$E$14), 0)

 

[jerminski73]

Perfect, you are the bomb! Thanks again for the assist my friend!
New add-on and I hope this is the last change.
In column N is my product flag for fuel types, We can haul up to 4 product types. all of the Gas products flag as 1, all of the diesel products flag as 2 which is totally modifiable for me. I now have to figure out the billing rate based upon this criteria:
All Gas Load=8500 (up to 4 lines)
Mixed Gas & Diesel Load=8000 (up to 4 lines)
All Diesel Load=7200 (up to 4 lines)
So in Column O I want the result to calc based on the above.
In my head I changed my flagging rules to total the 4 rows based on document number in D. Then I created a VLOOKUP which has my criteria in it. I just cannot figure out how to have it tally so the formula will work.

 

[CorrelateSpilled]

I'm not sure if I understand 100% what you are looking for. May you upload an updated workbook with the values you expect the formula to produce in Column O?

 

[jerminski73]

I hope this makes sense

 

[CorrelateSpilled]

Input this formula into cell O3 and drag it down to the last row with data. In this formula $D$3:$D$15 refers to the first to last row with data in the Document # column while $N$3:$N$15 refers to the first to last row with data in the Product Flag column. The formula will SUM up the UNIQUE Product Flags for a Document # and store that SUM value into loadFlag. The value of loadFlag will either be:

• 0, meaning that row is part of a multi-product load.
• 1, meaning All Gas load. Here, the Billing Rate will either be 8500 or the value in the Total Qty (Column M), whichever is greater.
• 2, meaning All Diesel load. Here, the Billing Rate will either be 7200 or the value in the Total Qty (Column M), whichever is greater.
• 3, meaning Mixed Gas/Diesel load. Here, the Billing Rate will either be 8000 or the value in the Total Qty (Column M), whichever is greater.

=LET(loadFlag, IF(J3=COUNTIF($D$3:$D$15, D3), SUM(UNIQUE(FILTER($N$3:$N$15, ($D$3:$D$15=D3), 0))), 0), IFS(loadFlag=0, 0, loadFlag=1, MAX(8500, M3), loadFlag=2, MAX(7200, M3), loadFlag=3, MAX(8000, M3)))

 

[jerminski73]

Excel for dummies ain't gonna elevate me to your level!!! You have just put me into functions I had no clue existed! I still have a good 15 years of Excelling left in me, got any online courses you suggest? Lol.

OK, I have a similar XLS only much more involved. It has 6 tabs.

All the same raw data but completely different conversions (different customer, different requirements, different rules)
Info in the doc, tab called Info

Raw Data is A:AS. All of my flags and calcs are in the AT:BC columns. I highlighted my problem children PINK and added comments to the cells. Most of my issues come from not being able to differentiate between the single item load and the multi item loads.