VBA: Using SUMIF on column H when value in column A is unique

[Yamezz]

I have exported sales data to work with that has order numbers in Column A and shipping costs in column H. Unfortunately the export includes the same shipping value on all lines of the order, despite just the one shipping charge being applied per order. For example:

Shopify to Xero.xlsm
	A	B	C	D	E	F	G	H
1	Order #	Paid	Date	SKU	Quantity	Price	Currency	Shipping
2	#1094	TRUE	6/12/2023	1	VER21DOCLD	25	AUD	22
3	#1094	TRUE	6/12/2023	1	CAS17DOCLD	35	AUD	22
4	#1097	TRUE	13/12/2023	1	TEM18DOCLD	30	AUD	22
5	#1097	TRUE	14/12/2023	1	SOT19DOCLD	40	AUD	22
6	#1098	TRUE	15/12/2023	2	VER21DOCLD	25	AUD	22
MrExcel

Both Order #1094 and #1097 were only charged a single value of $22 shipping each. How can I get a sum of all the actual shipping charges in this range? The correct answer is 2 x $22 = $44.

Is there a way to sum column H where column A is unique?

 

[Yamezz]

Hmm, I still get a Type Mismatch error.
The adapted code is now

ShippingDiscount = Evaluate("=SUMPRODUCT(IFERROR(1/COUNTIF(A" & FirstInvRow & ":A" & LastInvRow & ",A" & FirstInvRow & ":A" & LastInvRow & "),0),(N" & FirstInvRow & ":N" & LastInvRow & "))")

 

[Legacy 143009]

For this configuration, it works fine for me:

 

[Alex Blakenburg]

Hmm, I still get a Type Mismatch error.
The adapted code is now

ShippingDiscount = Evaluate("=SUMPRODUCT(IFERROR(1/COUNTIF(A" & FirstInvRow & ":A" & LastInvRow & ",A" & FirstInvRow & ":A" & LastInvRow & "),0),(N" & FirstInvRow & ":N" & LastInvRow & "))")

You need to show all your code.
You will get that error if either FirstInvRow or LastInvRow is zero.

 

[Yamezz]

Flash, it certainly works when there are numbers to sum, so I get the same result as you for above. However, in the current case, the data is filtered to:

Both FirstInvRow and LastInvRow evaluate to 243.

If I understand correctly, the Type Mismatch error is because SUMPRODUCT is expecting to sum numbers, of which there are none in this particular case.
Perhaps I need some way of determining if there are numbers to sum, and if not, then set ShippingDiscount to zero.

 

[Alex Blakenburg]

You did not mention until your last post that you needed it to work on filtered data. That changes things quite a bit.

Try replacing that one line with these lines which is a modification of @Flashbond's formula:

    Dim sFormula As String
    
    sFormula = "=SUMPRODUCT(IFERROR(1/COUNTIF(A~1:A~2,A~1:A~2),0)*(N~1:N~2)*SUBTOTAL(103,OFFSET(A~1,ROW(A~1:A~2)-MIN(ROW(A~1:A~2)),0)))"
    sFormula = Replace(sFormula, "~1", FirstInvRow)
    sFormula = Replace(sFormula, "~2", LastInvRow)

    ShippingDiscount = Evaluate(sFormula)

 

[Yamezz]

You're right, Alex, I forgot to mention the filter.
I've done some testing on a few different scenarios and your code correctly evaluates all the scenarios I've tested. Thanks.
Out of interest, why the Replace method, rather than entering FirstInvRow in the formula directly? I've never come across Replace before.

 

[Alex Blakenburg]

It is a fairly common practice although most people use a single symbol such as "@" as placeholders.
It is fairly tedious and high maintenance to convert the original Excel formula to use variables.

eg

Excel formula: =SUMPRODUCT((A1:A10<>"")*(N1:N10>10)*(B1:B10="Yes"))

To convert to a formula for use in evaluate, all you need to do is add a quote mark to the beginning and end and double any exising quotes
eg

Evaluate("=SUMPRODUCT((A1:A10<>"""")*(N1:N10>10)*(B1:B10=""Yes""))")

However to replace the 1 with a variable for the firstrow and the 10 with a variable for the lastrow, we need to break that string up into many pieces
eg

"=SUMPRODUCT((A" & firstrow & ":A" & lastrow &  .......... etc

This is tedious and error prone.
It is simpler to replace the 1's with the value of firstrow and the 10's with the value of lastrow. However to play it safe we don't use the actual row numbers because they may appear in other parts of the formula ie 1 is in 10 and there is also a criteria using 10 (">10"). So we use placeholders in those positions instead which are unlikely to be characters used in the formula. Most common seems to be "@", I have used ~1 and ~2, partly because I needed 2 placeholders and partly because I find it easier to spot.
Most people will nest the replace formulas, I have used multiple lines.

 

[Yamezz]

Thanks Alex.