Multiple "IF" or Conditions
 

I have a cell that has the following formula:

=SUMIF(F3:J18,A24,G3:K18)

Basically what this does is add a series of numbers if a certain company is
charging me money for a specific issue. Each Issue is one row and may have up
to 3 companies charging me money.

Now there is another column that asks if this is a change order or not. The
options are "yes" or "no".

As it stands now the above formula adds numbers regardless of whether or not
I select yes or no. I only want it to add if column O reads yes.

Would that be AND formula?

Thanks,
Neil M

Multiple "IF" or Conditions
 

Try this:

=SUMPRODUCT((F3:J18=A24)*(O3:O18="Yes")*G3:K18)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Neil M" wrote in message
...
I have a cell that has the following formula:

=SUMIF(F3:J18,A24,G3:K18)

Basically what this does is add a series of numbers if a certain company is
charging me money for a specific issue. Each Issue is one row and may have
up
to 3 companies charging me money.

Now there is another column that asks if this is a change order or not. The
options are "yes" or "no".

As it stands now the above formula adds numbers regardless of whether or not
I select yes or no. I only want it to add if column O reads yes.

Would that be AND formula?

Thanks,
Neil M

Multiple "IF" or Conditions
 

Unfortunately not that simple.
If you have Excel 2007 you can use the new "SUMIFS" function which will
allow multiple criteria which would suit you perfectly.

A simple workaround for you folder versions would be to add a column to use
as the criteria check, so eg in column Q use:
IF(O3="yes",F3,"")

similarly in R refer to H, in S to J
And now use Q3:S18 to compare against $A$24 instead of column F:J
=SUMIF(Q3:S18,A24,G3:K18)
(you can hide columns Q to S of course)


"Neil M" wrote:

I have a cell that has the following formula:

=SUMIF(F3:J18,A24,G3:K18)

Basically what this does is add a series of numbers if a certain company is
charging me money for a specific issue. Each Issue is one row and may have up
to 3 companies charging me money.

Now there is another column that asks if this is a change order or not. The
options are "yes" or "no".

As it stands now the above formula adds numbers regardless of whether or not
I select yes or no. I only want it to add if column O reads yes.

Would that be AND formula?

Thanks,
Neil M

Multiple "IF" or Conditions
 

I have 2007, BUt I cannot get the formula to work. I noticed that the SUMIFS
changes thing a bit in that the first argument is the actual sum range and
not the range.

By that logic I tried this:

=SUMIFS(G3:K18,F3:J18,B23,O3:O18,"yes")

If I am understanding the formula it should be
SUMIFS(sum range,criteria range1criteria1,criteria range2, criteria2)

But that results in a value error

"AdamV" wrote:

Unfortunately not that simple.
If you have Excel 2007 you can use the new "SUMIFS" function which will
allow multiple criteria which would suit you perfectly.

A simple workaround for you folder versions would be to add a column to use
as the criteria check, so eg in column Q use:
IF(O3="yes",F3,"")

similarly in R refer to H, in S to J
And now use Q3:S18 to compare against $A$24 instead of column F:J
=SUMIF(Q3:S18,A24,G3:K18)
(you can hide columns Q to S of course)


"Neil M" wrote:

I have a cell that has the following formula:

=SUMIF(F3:J18,A24,G3:K18)

Basically what this does is add a series of numbers if a certain company is
charging me money for a specific issue. Each Issue is one row and may have up
to 3 companies charging me money.

Now there is another column that asks if this is a change order or not. The
options are "yes" or "no".

As it stands now the above formula adds numbers regardless of whether or not
I select yes or no. I only want it to add if column O reads yes.

Would that be AND formula?

Thanks,
Neil M

Multiple "IF" or Conditions
 

That might work if I was looking for a product, but I need the sum of a range.

"RagDyeR" wrote:

Try this:

=SUMPRODUCT((F3:J18=A24)*(O3:O18="Yes")*G3:K18)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Neil M" wrote in message
...
I have a cell that has the following formula:

=SUMIF(F3:J18,A24,G3:K18)

Basically what this does is add a series of numbers if a certain company is
charging me money for a specific issue. Each Issue is one row and may have
up
to 3 companies charging me money.

Now there is another column that asks if this is a change order or not. The
options are "yes" or "no".

As it stands now the above formula adds numbers regardless of whether or not
I select yes or no. I only want it to add if column O reads yes.

Would that be AND formula?

Thanks,
Neil M

Multiple "IF" or Conditions
 

If you try it, you'll like it!<bg

It DOES sum the range.

When asking for help, it's usually a good idea to try out a suggestion
*before* you make any comments.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Neil M" wrote in message
...
That might work if I was looking for a product, but I need the sum of a
range.

"RagDyeR" wrote:

Try this:

=SUMPRODUCT((F3:J18=A24)*(O3:O18="Yes")*G3:K18)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Neil M" wrote in message
...
I have a cell that has the following formula:

=SUMIF(F3:J18,A24,G3:K18)

Basically what this does is add a series of numbers if a certain company
is
charging me money for a specific issue. Each Issue is one row and may
have
up
to 3 companies charging me money.

Now there is another column that asks if this is a change order or not.
The
options are "yes" or "no".

As it stands now the above formula adds numbers regardless of whether or
not
I select yes or no. I only want it to add if column O reads yes.

Would that be AND formula?

Thanks,
Neil M

Multiple "IF" or Conditions
 

I DID try it and that is why I replied letting yuo know that it didn't work.
If it had worked my reply would have been, "Thank You".

When assuming someone didnt try something, it is usually a good idea to ask
them first if they, in fact did try it *before* commenting on their
methodolgy.

"RagDyer" wrote:

If you try it, you'll like it!<bg

It DOES sum the range.

When asking for help, it's usually a good idea to try out a suggestion
*before* you make any comments.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Neil M" wrote in message
...
That might work if I was looking for a product, but I need the sum of a
range.

"RagDyeR" wrote:

Try this:

=SUMPRODUCT((F3:J18=A24)*(O3:O18="Yes")*G3:K18)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Neil M" wrote in message
...
I have a cell that has the following formula:

=SUMIF(F3:J18,A24,G3:K18)

Basically what this does is add a series of numbers if a certain company
is
charging me money for a specific issue. Each Issue is one row and may
have
up
to 3 companies charging me money.

Now there is another column that asks if this is a change order or not.
The
options are "yes" or "no".

As it stands now the above formula adds numbers regardless of whether or
not
I select yes or no. I only want it to add if column O reads yes.

Would that be AND formula?

Thanks,
Neil M

Multiple "IF" or Conditions
 

Without getting into semantics, you stated that you weren't looking for a
product, but for a sum ... and NO mention that the formula didn't work.
And that is what the suggested formula returns ... a SUM of values in the
cells within the range of G3 to K18, where the *previous* column contains
the identical value that's in A24.
It returns *exactly* what YOUR Sumif() formula returns, with the added
functionality of checking if the adjacent rows in Column O contain the text
value of "Yes".

Now, if you can elaborate on exactly what you meant in your last post when
you said "it didn't work".
..
Did it return:
No answer,
Wrong answer,
Error message,
What exactly was the problem?

You must realize that when the folks around here post a suggested solution,
they usually have NO knowledge of your datalist configuration and make-up.
Their test sheets are almost always populated with keyed in test values,
where your actual datalist may be comprised of returns from other formulas,
or values imported from other apps, or even the Web.

Therefore, a concise description of any problems encountered with a
suggestion would be helpful to anyone reading this thread and wishing to
contribute alternate solutions and/or ideas.

For this case in point, my testing was done on all numeric values in the
range, and a numeric value in A24.

If you can describe your datalist configuration and the actual returns of
the suggested formula, we, or anyone else reading this thread, may perhaps
be able to come up with a viable, workable formula for you.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------




"Neil M" wrote in message
...
I DID try it and that is why I replied letting yuo know that it didn't
work.
If it had worked my reply would have been, "Thank You".

When assuming someone didnt try something, it is usually a good idea to
ask
them first if they, in fact did try it *before* commenting on their
methodolgy.

"RagDyer" wrote:

If you try it, you'll like it!<bg

It DOES sum the range.

When asking for help, it's usually a good idea to try out a suggestion
*before* you make any comments.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Neil M" wrote in message
...
That might work if I was looking for a product, but I need the sum of a
range.

"RagDyeR" wrote:

Try this:

=SUMPRODUCT((F3:J18=A24)*(O3:O18="Yes")*G3:K18)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Neil M" wrote in message
...
I have a cell that has the following formula:

=SUMIF(F3:J18,A24,G3:K18)

Basically what this does is add a series of numbers if a certain
company
is
charging me money for a specific issue. Each Issue is one row and may
have
up
to 3 companies charging me money.

Now there is another column that asks if this is a change order or
not.
The
options are "yes" or "no".

As it stands now the above formula adds numbers regardless of whether
or
not
I select yes or no. I only want it to add if column O reads yes.

Would that be AND formula?

Thanks,
Neil M