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

[CorrelateSpilled]

Sorry for the late reply; I have been out of town for the past week.

I haven't gone through everything in the latest spreadsheet but for your Gallon Total (Column AX), the range you are COUNTIF is off. For example, in row 34 of your Gallon Total (Column AX), you are only COUNTIFS row 30; instead you should COUNTIF starting from row 34 (which is the first row of data in your spreadsheet) to row 249 (which is the last row of data in your spreadsheet). Also, I realized the OR within the formula was unnecessary so the formula can be simplified to:

=IF($AU34=COUNTIFS($A$34:$A249, $A34, $J$34:$J249, J34), SUMIFS($S$34:S249, $A$34:A249, A34, $J$34:J249, J34), 0)

Input the formula into cell AX34 (which is the first row of data) in the Gallon Total (column AX), then drag it down to the last row of data in that column. Also, for your Minimum Product Gallons (Column AW) it looks like you are COUNTIF and FILTERing the LoadingBOL (Column E) rather than the BillOfLadingId (Column A) so I think the formula should be:

=LET(loadFlag, IF(AU34=COUNTIF($A$34:A40, A34), SUM(UNIQUE(FILTER($AV$34:AV40, ($A$34:A40=A34), 0))), 0), IFS(loadFlag=0, 0, loadFlag=1, MAX(8500, F34), loadFlag=2, MAX(7200, F34), loadFlag=3, MAX(8000, F34)))

Input the formula into cell AW34 (the first row of data in your Minimum Product Gallons column and drag it down to the last row of data.

 

[CorrelateSpilled]

In your Minimums (Column BA) you check if Load Billed (Column AZ) is not zero before doing a SUM; you can employ the same trick to the Surcharge (Column BB) so rather than checking if Load Count (Column AT) is zero, check if Load Billed (Column AZ) is not zero. The updated formula for the Surcharge column would become:

=IF(AZ34=0,0,SUM(AE34:AQ34))

Input that formula to cell BB34 (the first row of data in the Surcharge column) and drag it down to the last row of data in that column. You can employ a similar trick to the formula for the Load Billed column by checking if the Gallon Total (Column AX) is not zero before doing the SUMIFS. The updated formula for the Load Billed (Column AZ) would be:

=IF($AX34=0, 0, SUMIFS($AY$34:AY41, $A$34:A41, A34, $J$34:J41, J34))

Input that formula to cell AZ34 (the first row of data in the Load Billed column) and drag it down to the last row of data in that column.

 

[jerminski73]

Thanks for the reply and the replacement xls, I will have to study a little more to see if this is the fix as I know nothing about Power Query lol

I think this project could be solved elegantly with Power Query, aka Get & Transform Data, which has been built-in to Excel since version 2016.
As a very basic start, in the attached I've created 2 small output tables from a copy of your Convertor sheet (Convertor (2), which I've adjusted a little).
The first output table is at cell BF1 of the Convertor (2) sheet and shows summary data by grouping only on BillOfLadingId and Column4 (Column J of your source data). The count column shows how many rows of your source data go into each row of the result table. Ignore the MinIdx column, it's just there to retain the sort order of that table to the same as your source data.
The second output table is at cell A1 of Sheet2. The only difference is that it has additionally been grouped by freight type (Diesel/Gas).
I've only shown Load Billed and Gallon Total columns on theses outputs - so clearly there's more needed.
If the source data changes, these tables need to be updated by clicking on the Refresh button on the Queries & Connections section of the Data tab of the ribbon.

Could this be a way forward?

For the future:

You say:
"The raw data comes straight out of the billing software and goes straight into columns A:AS."
You're very likely going to be able to connect directly to the billing software data using Power Query and you probably need never see that raw data.

 

[jerminski73]

Holy guacamole! This thing is fully functional, I cannot thank you enough for the assist!!

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)))