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?

 

[Legacy 143009]

Hi,

Why are you not including 1098? Must be 3x22?

 

[Yamezz]

Sorry, that's my mistake in supplying some 'random' data to work with. Excel's fill function filled a series instead of copy. It should be just #1094 and #1097 showing.

 

[Legacy 143009]

I am not sure if vba is needed for this. Try to put this formula in H7:

=SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6),(H2:H6))

 

[Yamezz]

This is the last step in a macro to take data from a .csv file exported from Shopify and organise it into another .csv file that suits the Xero accounting software. I need to find the total shipping charged to customers for the period that the report covers and enter that into Xero. Hopefully without having to manually type in a single value.

 

[Legacy 143009]

Sub test()
  Dim totalShipping As Double, lRow As Long
  lRow = Cells(Rows.Count, "A").End(xlUp).Row
  totalShipping = Evaluate("=SUMPRODUCT(1/COUNTIF(A2:A" & lRow & ",A2:A" & lRow & "),(H2:H" & lRow & "))")
  MsgBox totalShipping
End Sub

 

[Yamezz]

Thank you so much!

 

[Legacy 143009]

You are welcome

 

[Yamezz]

This solution has been working well. I've used it in other iterations too, up until today when I have encountered a situation where the column in question contains no data. Ideally the data I'm working with would contain "0" when there is no value to consider, but the data I receive is just blank in that column instead, so I get a Type Mismatch error. Is there a way to handle the odd case that there is no data to sum in the particular column, and so the variable will just be set to zero?

 

[Legacy 143009]

Hi,

Are you asking something like this:

totalShipping = Evaluate("=SUMPRODUCT(IFERROR(1/COUNTIF(A2:A" & lRow & ",A2:A" & lRow & "),0),(H2:H" & lRow & "))")