Sum of Values - Based on The Cell Next To Them

Hi There,

I’m still getting to grips with variables and I was wondering if maybe someone could help me out?

I’m trying to create a script that adds together values, based on the cells next to them. For example, I have a sheet containing recurring sets of data, a rough example below. I need to look for every instance of of each name in Column A and show the sum total of the value that appears next to it in column B.

The names and the order in which they appear will always be the same. However, I might have 3 sets of data, as below, or I might have 50.

Adam 40
Jane 12
Bobby 30
Dave 6

Adam 40
Jane 60
Bobby 11
Dave 33

Adam 2
Jane 15
Bobby 14
Dave 12

Expected Result for the script would therefore be:

Adam 82
Jane 87
Bobby 55
Dave 51

I’m not sure that it’s a problem to treat with AppleScript.
Numbers offer a function doing exactly that.

When the app is run in French the formula would be : =SOMME.SI(A;“Adam”;B)
I assume that in English it would be = SUMIF(A,“Adam”,B) but I’m not sure that the English version of SOMME.SI is SUMIF.

As you ask about AppleScript I will give a possible scheme

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set theNames to value of cells of column 1
	set theQuantities to value of cells of column 2
end tell

set sumAdam to 0
set sumJane to 0
set sumBobby to 0
set sumDave to 0
set theRow to 0
set theKey to "Adam"
repeat with aName in theNames
	set aName to aName as text
	set theRow to theRow + 1
	if aName = "Adam" then
		set sumAdam to sumAdam + (theQuantities's item theRow)
	else if aName = "Jane" then
		set sumJane to sumJane + (theQuantities's item theRow)
	else if aName = "Bobby" then
		set sumBobby to sumBobby + (theQuantities's item theRow)
	else if aName = "Dave" then
		set sumDave to sumDave + (theQuantities's item theRow)
	end if
end repeat

{sumAdam as integer, sumJane as integer, sumBobby as integer, sumDave as integer}

Edited to display totals as integer values.

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) mardi 29 novembre 2016 14:22:15

Thanks Yvan, this is perfect! Exactly what I was looking for.

Although I know this can be solved within numbers without the use of Applescript, this will be part of a larger reporting script.

One question, is it possible to show the numbers without the decimal places? so 7.0 would just appear as 7 ?

I edited my message so that you get totals as integer values.

Here is an alternate scheme which may be interesting if the number of names may vary.

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set theNames to value of cells of column 1
	set theQuantities to value of cells of column 2
end tell

set myNames to {"Adam", "Jane", "Bobby", "Dave"} # So you may easily add new names to treat
set sums to {}
# Build a list of totals to calculate.
repeat with i from 1 to count myNames
	set end of sums to 0
end repeat
set theRow to 0
# Scan the list of values
repeat with aName in theNames
	set aName to aName as text
	set theRow to theRow + 1
	# Scan the list of names
	repeat with i from 1 to count myNames
		if aName = (myNames's item i) as text then
			set sums's item i to ((sums's item i) + (theQuantities's item theRow)) as integer
			exit repeat
		end if
	end repeat
end repeat

sums --> {164, 174, 110, 102}

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) mardi 29 novembre 2016 15:46:39

Thanks Yvan :slight_smile:

I also answered my own question above - i did the following:
set sumAdam to sumAdam as integer

Which renders the vale as the closest whole number!