What does this formula mean?

Question

I have inherited a worksheet and I can see what the formula is adding up, but I don't know why this formula was used and what it does.  From my perspective a simple Sum formula would have suffice for this calculations, but maybe I am just making it out to be too simple.  Can someone explain to me what this formula means and what it does.

=SUM(($F$2:$F$121=$F$129)*(G$2:G$121))

Answer 1

Hi,

This formula can be simplified to:

=SUMIF($F$2:$F$121,$F$129,G$2:G$121)

This formula will add the numbers in G2:G121 if the criteria (in cell F129) is found in range F2:F121.

Hope this helps.

Answer 2

This is an array formula (entered using CTRL-ENTER, not just ENTER).  It is summing up items in Column G, but only those items in Column G where the value in Column F is equal to the value in $F$129.

This part:

($F$2:$F$121=$F$129) results in either TRUE or FALSE.  If TRUE, it becomes the equivalent of a value of one (1).  The example below shows you how this works.  Some of the values in Column F result in TRUE and some result in FALSE.  Only those that are TRUE add to the SUM.

	Column F	Column G	$F$? = $F$129?	Add to SUM:
Row 2	3	15	FALSE	0*15
Row 3	55	22	TRUE	1*22
Row 4	-17	13	FALSE	0*13
Row 5	55	87	TRUE	1*87
…	…	…	…	…
Row 121	55	19	TRUE	1*19
…
Row 129	55

Answer 3

Hi,

This formula can be simplified to:

=SUMIF($F$2:$F$121,$F$129,G$2:G$121)

This formula will add the numbers in G2:G121 if the criteria (in cell F129) is found in range F2:F121.

Hope this helps.

Thank you Ashish, I used your formula and it worked.  Thankyou so much for your help!

Answer 4

Thank you E.J. for your help in explaing the formula.  Although I could not make the formula work, eventhough I used CTRL-ENTR intead of just ENTR.  I used the suggestion that Ashish has below and I was able to make it work.