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