Originally Posted by Vacuum Sealed

On 3/05/2012 9:21 PM, Spencer101 wrote:
LiziC;1601435 Wrote:
Hi,

I'm using the below SumIf equation, however the range in Column G is a
formula and so the sum always comes out to 0.

Is there a way to get the below forumla to only sum the values in H if
the formula in G shows a value, and ignore anything cells in G that are
blank?

(The formula in Column G is part of a IF equation with a VLOOKUP to a
seperate spreadsheet)

=SUMIF(G25:G80,"<1",H25:H80)

Have you tried using =SUMIFS() rather than =SUMIF(). I think only
available in 2007/2010. It allows more than one condition.

Or if using earlier than 2007 you could use =SUMPRODUCT() to do this for
you.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+




Firstly

A SumIf or SumProduct will not evaluate when there are #Value cells.

=IF(G2="",0,(I2/G2)), this will give any blank cells a value of 0.

Interestingly that your above formula points to Column I which has no
values at all so I can only assume they are manually entered or they are
apart of a lookup, of which you have not included the formula for.

Your SumIf formula is looking for values less than 1 which will always
return 0 unless there are values of 0.99 or less, so I assume it should
be the following: =SUMIF(G2:G56,"0",H2:H56)...

HTH
Mick.