Countif & sumif combined required to solve answer - please help

[ACLARKE]

Hi Guys,

Need help please!

See attached file, "Test" and the below example;

I need a formula that will do the following;

Count the number of Basic Charges (in column E = yellow) that are sent on the same date (in column F = purple) to the same post code (in column T = blue) and provide an adjustment (in column AB = orange) for the sum of the Basic Charges that match this criteria less the lowest Basic Charge within the same criteria.

For example;

Let's assume the below are sent to the same post code on the same date;

Basic1 = $5
Basic2 = $10
Basic3 = $15

The adjustment would be the sum of Basic1, Basic2 & Basic3 = $30 - the lowest Basic Charge (Basic1) = $5
Adjustment = $30 - $5 = $25

$25 is the answer

This is known as a consolidation adjustment where the client is to pay for the lowest Basic Charge and be credited for all other Basic Charges sent to the same post code on the same date.

I have sorted the attached file in date and post code order. Can you please assume this sort is not done when you are providing your formula solution. It would be preferable to have the formula without sorting first. If you can only provide the formula by sorting the above first please advise that in your answer.

Please supply the formula below to be entered into column AB (orange) on the attached file, "Test".

Thanks in advance!

[Ace_XL]

try this array formula..in row 2

=SUMIFS(E:E,F:F,F2,T:T,T2)-MIN(IF(($F$2:$F$201=F2)*($T$2:$T$201=T2),$E$2:$E$201))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[ACLARKE]

TEST.xlsx

Thanks ACE almost there!!

See attached updated file, "Test"

I have placed the Correct Answer in column AC in pink. Refer to the comments in cell AD5 in red for an explanation of what is requied, i don't think i quite explained it correctly in the original post. These comments apply to all scenarios where the lowest basic charge value out of the range is not adjusted but the other highest basic charge values are.

Can you please tweak the formual to suit the above and resend.

Thanks in advance!!!!

[Ace_XL]

Ok..how about

=IF(COUNTIFS($F$2:F2,F2,$T$2:T2,T2)>1,0,SUMIFS(E:E,F:F,F2,T:T,T2)-MIN(IF(($F$2:$F$201=F2)*($T$2:$T$201=T2),$E$2:$E$201)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

This will give zero in AB6 and 10 in AB5. Is this something you can live with?

[ACLARKE]

Thanks Ace.

It works for the rows that only have 2 movements on the same date to the same post code but as soon as you go to 3 or more the adjustment value is consolidated into one (1) total amount.

Unfortunately i need the split adjustment for each row - not consolidated as we adjust per consignment note which is also attached to a specific account number.

Can you please retweak and send again for review.

Thanks again!!!!