SumIf equation question

[LiziC]

Hi,

I'm using the below SumIf equation, however the range in Column G is
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 i
the formula in G shows a value, and ignore anything cells in G that ar
blank?

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

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

+-------------------------------------------------------------------
|Filename: SUMIF.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=362
+-------------------------------------------------------------------

 

[Spencer101]

Hi,

I'm using the below SumIf equation, however the range in Column G is
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 i
the formula in G shows a value, and ignore anything cells in G that ar
blank?

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

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

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

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

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

 

[Claus Busch]

Hi Lizi,

Am Thu, 3 May 2012 08:40:55 +0000 schrieb LiziC:

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

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

in H2:
=IF(G2="","",I2/G2)
and fill down to H57
Then your SUMIF will work


Regards
Claus Busch

 

[Vacuum Sealed]

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.

 

[Mazzaropi]

I'm using 2003, but can't get the =SUMPRODUCT() to work.

Dear *LiziC*, Good Morning.

I´m confused with your explanation.

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

All the cells in H column that match with a cell in G column that ar
not BLANK are ZERO(0).
The column I is BLANK.

You can use *=SUMIF(G2:G57,">0",H2:H57)*
BUT the result will be ZERO.

Try this formula and put *5,000 in I51*.
The result will be different from zero.

Please, let me know if it worked as you desired.
Or try to show an example to easier the understanding.

I´ll be here to help you

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

 

[LiziC]

On 3/05/2012 9:21 PM, Spencer101 wrote:-

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 ar

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 shoul

be the following: =SUMIF(G2:G56,">0",H2:H56)...

HTH
Mick.

Hi Mick,

Thanks for pointing out the misuse of the <, it definitely should be >.

The funny thing is, this formula is now working even though m
spreadsheet still has #Value cells all over this place. Is there an
chance that this is because my personal laptop I'm on now is Excel 2010
but at work I'm using 2003?

Li

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

 

[Vacuum Sealed]

Hi Mick,

Thanks for pointing out the misuse of the<, it definitely should be>.

The funny thing is, this formula is now working even though my
spreadsheet still has #Value cells all over this place. Is there any
chance that this is because my personal laptop I'm on now is Excel 2010,
but at work I'm using 2003?

Liz


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

Hi Liz

I'm not 100% sure if that is the case, though with the advancements of
2010 it may very well overlook the affected cells as predictable errors
and only sum the valid values. It is one for the guru's to clarify I
should imagine.

Cheers
Mick.