A AK New Member Feb 17, 2010 #1 Why and how are 2 consecutive hyphens used in an Excel formula? e.g. what is the meaning of =sumproduct(--(a1:a4="x"),(b1:b4))
Why and how are 2 consecutive hyphens used in an Excel formula? e.g. what is the meaning of =sumproduct(--(a1:a4="x"),(b1:b4))
Hui Excel Ninja Staff member Feb 17, 2010 #2 AK Sumproduct works on multiplying each of the values in brackets by the next set of data in brackets from the same position and then adding it all up. In your case it evaluates the (a1:a4="x") for each value in the range a1:a4 the value in each case will be True or false depending on the values in A1:A4 True or False don't have a stored value in Excel ie: 1 or 0 so to convert them people use a variety of techniques Multiplying a True/False by 1 or in this case -- converts the True/False to either a 1 or 0 in your case --(a1:a4="x") is equivalent to -1*-1*(a1:a4="x") I prefer a simple 1*(a1:a4="x") as its easier to read
AK Sumproduct works on multiplying each of the values in brackets by the next set of data in brackets from the same position and then adding it all up. In your case it evaluates the (a1:a4="x") for each value in the range a1:a4 the value in each case will be True or false depending on the values in A1:A4 True or False don't have a stored value in Excel ie: 1 or 0 so to convert them people use a variety of techniques Multiplying a True/False by 1 or in this case -- converts the True/False to either a 1 or 0 in your case --(a1:a4="x") is equivalent to -1*-1*(a1:a4="x") I prefer a simple 1*(a1:a4="x") as its easier to read