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.

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.

HELP from BRAZIL <<<<

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 values in H if the formula in G shows a value, and ignore anything cells in G that are blank?

All the cells in H column that match with a cell in G column that are 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.

Hi Mazzaropi,

Sorry the file I had attached is cut from another spreadsheet I'm working on but that has company information on it which I'm relucant to post. However I have attached a new file that has info in I that makes the formula in H work.

Basically what I need is a formula that will total all the lines in H but will ignore anything that has a #VALUE!.

My idea was to use G as part of a SUMIF equation to total H but because G & H are already part of an IF/VLookUp formula the cell will never show an actual value. And that's my issue, is it possible to get the formula to work and disregard the formula?

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

On 4/05/2012 6:11 AM, LiziC wrote:
Vacuum Sealed;1601456 Wrote:
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.

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.