SUMIFS using an "Or" condition?
 

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

As a side note, the criteria I am using in the two columns are text rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

CG;414422 Wrote:
As a side note, the criteria I am using in the two columns are text
rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria
from
different columns, but I need it to be an "either/or" formula. Is
there a
way to do this with SUMIFs, or should I use a different formula?

Hi,
something like =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) perhaps ?
( extracted from
http://www.xldynamic.com/source/xld....T.html#classic


--
Pecoflyer

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115416

SUMIFS using an "Or" condition?
 

I forgot to mention that both columns may have rows that have the same
criteria in both columns, so I don't want to double count the ones that do.
For example:

Col A Col B
corp non
corp corp
non corp

I want to sum the data where either col A or col B has corp. If they both
have that word, that is fine, but I don't want to count it twice.

"Pecoflyer" wrote:


CG;414422 Wrote:
As a side note, the criteria I am using in the two columns are text
rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria
from
different columns, but I need it to be an "either/or" formula. Is
there a
way to do this with SUMIFs, or should I use a different formula?

Hi,
something like =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) perhaps ?
( extracted from
http://www.xldynamic.com/source/xld....T.html#classic


--
Pecoflyer

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115416

SUMIFS using an "Or" condition?
 

Hi

Not very clear as to what you want to achieve, would you post an example
showing the before and after result?
Further, pls indicate if you are using Excel 2007 or earlier version

General formula to use for counting with more than more 2 criteria
are :
Prior to XL2007
1) SUMPRODUCT(--(Range1="Criteria1"),--(Range2="Criteria2")) or
2) SUM((Range1="Criteria1")*(Range2="Criteria2"))

XL2007

COUNTIFS(Range1,"Criteria1",Range2,"Criteria2")


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CG" wrote:

As a side note, the criteria I am using in the two columns are text rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

Try this formula:

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0))

Hope this helps / Lars-Åke

On Mon, 13 Jul 2009 07:35:01 -0700, CG
wrote:

I forgot to mention that both columns may have rows that have the same
criteria in both columns, so I don't want to double count the ones that do.
For example:

Col A Col B
corp non
corp corp
non corp

I want to sum the data where either col A or col B has corp. If they both
have that word, that is fine, but I don't want to count it twice.

"Pecoflyer" wrote:


CG;414422 Wrote:
As a side note, the criteria I am using in the two columns are text
rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria
from
different columns, but I need it to be an "either/or" formula. Is
there a
way to do this with SUMIFs, or should I use a different formula?

Hi,
something like =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) perhaps ?
( extracted from
http://www.xldynamic.com/source/xld....T.html#classic


--
Pecoflyer

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115416

SUMIFS using an "Or" condition?
 

=SUMPRODUCT(SIGN((A1:A10="corp")+(B1:B10="corp")))

--
__________________________________
HTH

Bob

"CG" wrote in message
...
I forgot to mention that both columns may have rows that have the same
criteria in both columns, so I don't want to double count the ones that
do.
For example:

Col A Col B
corp non
corp corp
non corp

I want to sum the data where either col A or col B has corp. If they both
have that word, that is fine, but I don't want to count it twice.

"Pecoflyer" wrote:


CG;414422 Wrote:
As a side note, the criteria I am using in the two columns are text
rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria
from
different columns, but I need it to be an "either/or" formula. Is
there a
way to do this with SUMIFs, or should I use a different formula?

Hi,
something like =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) perhaps ?
( extracted from
http://www.xldynamic.com/source/xld....T.html#classic


--
Pecoflyer

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !
------------------------------------------------------------------------
Pecoflyer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=115416

SUMIFS using an "Or" condition?
 

Here's an example:

Col A Col B Col C
corp stock $100
muni bond $200
corp corp $50
non-sec corp $150
UST bond $300

I want to sum the amounts in Col C, for which Col A, B, or A and B together
have the word "corp". So in this example, I would want to sum the amounts on
Lines 1, 3, and 4, to get a sum of $300. Since Line 3 has "corp" in both A
and B columns, I don't want the formula to count it twice.

"Francis" wrote:

Hi

Not very clear as to what you want to achieve, would you post an example
showing the before and after result?
Further, pls indicate if you are using Excel 2007 or earlier version

General formula to use for counting with more than more 2 criteria
are :
Prior to XL2007
1) SUMPRODUCT(--(Range1="Criteria1"),--(Range2="Criteria2")) or
2) SUM((Range1="Criteria1")*(Range2="Criteria2"))

XL2007

COUNTIFS(Range1,"Criteria1",Range2,"Criteria2")


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CG" wrote:

As a side note, the criteria I am using in the two columns are text rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

I am using XL 2007

"Francis" wrote:

Hi

Not very clear as to what you want to achieve, would you post an example
showing the before and after result?
Further, pls indicate if you are using Excel 2007 or earlier version

General formula to use for counting with more than more 2 criteria
are :
Prior to XL2007
1) SUMPRODUCT(--(Range1="Criteria1"),--(Range2="Criteria2")) or
2) SUM((Range1="Criteria1")*(Range2="Criteria2"))

XL2007

COUNTIFS(Range1,"Criteria1",Range2,"Criteria2")


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CG" wrote:

As a side note, the criteria I am using in the two columns are text rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

Try this:

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0),C1:C10)

Hope this helps / Lars-Åke

On Mon, 13 Jul 2009 10:56:01 -0700, CG
wrote:

Here's an example:

Col A Col B Col C
corp stock $100
muni bond $200
corp corp $50
non-sec corp $150
UST bond $300

I want to sum the amounts in Col C, for which Col A, B, or A and B together
have the word "corp". So in this example, I would want to sum the amounts on
Lines 1, 3, and 4, to get a sum of $300. Since Line 3 has "corp" in both A
and B columns, I don't want the formula to count it twice.

"Francis" wrote:

Hi

Not very clear as to what you want to achieve, would you post an example
showing the before and after result?
Further, pls indicate if you are using Excel 2007 or earlier version

General formula to use for counting with more than more 2 criteria
are :
Prior to XL2007
1) SUMPRODUCT(--(Range1="Criteria1"),--(Range2="Criteria2")) or
2) SUM((Range1="Criteria1")*(Range2="Criteria2"))

XL2007

COUNTIFS(Range1,"Criteria1",Range2,"Criteria2")


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CG" wrote:

As a side note, the criteria I am using in the two columns are text rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

I just tried this formula below and I am getting zero as the amount. I had
to use a * behind the word "corp" ("corp*") because some of the cells have
just the word "corp" and some have "corp" plus another word, and I need any
cell that has the word "corp" in it. Would that affect the formula?

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0),C1:C10)



"Lars-Ã…ke Aspelin" wrote:

Try this:

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0),C1:C10)

Hope this helps / Lars-Ã…ke

On Mon, 13 Jul 2009 10:56:01 -0700, CG
wrote:

Here's an example:

Col A Col B Col C
corp stock $100
muni bond $200
corp corp $50
non-sec corp $150
UST bond $300

I want to sum the amounts in Col C, for which Col A, B, or A and B together
have the word "corp". So in this example, I would want to sum the amounts on
Lines 1, 3, and 4, to get a sum of $300. Since Line 3 has "corp" in both A
and B columns, I don't want the formula to count it twice.

"Francis" wrote:

Hi

Not very clear as to what you want to achieve, would you post an example
showing the before and after result?
Further, pls indicate if you are using Excel 2007 or earlier version

General formula to use for counting with more than more 2 criteria
are :
Prior to XL2007
1) SUMPRODUCT(--(Range1="Criteria1"),--(Range2="Criteria2")) or
2) SUM((Range1="Criteria1")*(Range2="Criteria2"))

XL2007

COUNTIFS(Range1,"Criteria1",Range2,"Criteria2")


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CG" wrote:

As a side note, the criteria I am using in the two columns are text rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

=SUMPRODUCT(--(((LEFT(A1:A10,4)="corp")+(LEFT(B1:B10,4)="corp")) 0),C1:C10)

Lars-Åke

On Mon, 13 Jul 2009 11:24:01 -0700, CG
wrote:

I just tried this formula below and I am getting zero as the amount. I had
to use a * behind the word "corp" ("corp*") because some of the cells have
just the word "corp" and some have "corp" plus another word, and I need any
cell that has the word "corp" in it. Would that affect the formula?

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0),C1:C10)



"Lars-Åke Aspelin" wrote:

Try this:

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0),C1:C10)

Hope this helps / Lars-Åke

On Mon, 13 Jul 2009 10:56:01 -0700, CG
wrote:

Here's an example:

Col A Col B Col C
corp stock $100
muni bond $200
corp corp $50
non-sec corp $150
UST bond $300

I want to sum the amounts in Col C, for which Col A, B, or A and B together
have the word "corp". So in this example, I would want to sum the amounts on
Lines 1, 3, and 4, to get a sum of $300. Since Line 3 has "corp" in both A
and B columns, I don't want the formula to count it twice.

"Francis" wrote:

Hi

Not very clear as to what you want to achieve, would you post an example
showing the before and after result?
Further, pls indicate if you are using Excel 2007 or earlier version

General formula to use for counting with more than more 2 criteria
are :
Prior to XL2007
1) SUMPRODUCT(--(Range1="Criteria1"),--(Range2="Criteria2")) or
2) SUM((Range1="Criteria1")*(Range2="Criteria2"))

XL2007

COUNTIFS(Range1,"Criteria1",Range2,"Criteria2")


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CG" wrote:

As a side note, the criteria I am using in the two columns are text rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

If "corp" can be placed anywhere, not only in the beginning, you may
try this formula:

=SUMPRODUCT(1-ISERROR(FIND("corp",A1:A10))*ISERROR(FIND("corp",B 1:B10)),C1:C10)

Hope this helps / Lars-Åke


On Mon, 13 Jul 2009 18:48:56 GMT, Lars-Åke Aspelin
wrote:

=SUMPRODUCT(--(((LEFT(A1:A10,4)="corp")+(LEFT(B1:B10,4)="corp")) 0),C1:C10)

Lars-Åke

On Mon, 13 Jul 2009 11:24:01 -0700, CG
wrote:

I just tried this formula below and I am getting zero as the amount. I had
to use a * behind the word "corp" ("corp*") because some of the cells have
just the word "corp" and some have "corp" plus another word, and I need any
cell that has the word "corp" in it. Would that affect the formula?

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0),C1:C10)



"Lars-Åke Aspelin" wrote:

Try this:

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0),C1:C10)

Hope this helps / Lars-Åke

On Mon, 13 Jul 2009 10:56:01 -0700, CG
wrote:

Here's an example:

Col A Col B Col C
corp stock $100
muni bond $200
corp corp $50
non-sec corp $150
UST bond $300

I want to sum the amounts in Col C, for which Col A, B, or A and B together
have the word "corp". So in this example, I would want to sum the amounts on
Lines 1, 3, and 4, to get a sum of $300. Since Line 3 has "corp" in both A
and B columns, I don't want the formula to count it twice.

"Francis" wrote:

Hi

Not very clear as to what you want to achieve, would you post an example
showing the before and after result?
Further, pls indicate if you are using Excel 2007 or earlier version

General formula to use for counting with more than more 2 criteria
are :
Prior to XL2007
1) SUMPRODUCT(--(Range1="Criteria1"),--(Range2="Criteria2")) or
2) SUM((Range1="Criteria1")*(Range2="Criteria2"))

XL2007

COUNTIFS(Range1,"Criteria1",Range2,"Criteria2")


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CG" wrote:

As a side note, the criteria I am using in the two columns are text rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

Thank you so much. This has been very helpful.

"Lars-Ã…ke Aspelin" wrote:

If "corp" can be placed anywhere, not only in the beginning, you may
try this formula:

=SUMPRODUCT(1-ISERROR(FIND("corp",A1:A10))*ISERROR(FIND("corp",B 1:B10)),C1:C10)

Hope this helps / Lars-Ã…ke


On Mon, 13 Jul 2009 18:48:56 GMT, Lars-Ã…ke Aspelin
wrote:

=SUMPRODUCT(--(((LEFT(A1:A10,4)="corp")+(LEFT(B1:B10,4)="corp")) 0),C1:C10)

Lars-Ã…ke

On Mon, 13 Jul 2009 11:24:01 -0700, CG
wrote:

I just tried this formula below and I am getting zero as the amount. I had
to use a * behind the word "corp" ("corp*") because some of the cells have
just the word "corp" and some have "corp" plus another word, and I need any
cell that has the word "corp" in it. Would that affect the formula?

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0),C1:C10)



"Lars-Ã…ke Aspelin" wrote:

Try this:

=SUMPRODUCT(--(((A1:A10="corp")+(B1:B10="corp"))0),C1:C10)

Hope this helps / Lars-Ã…ke

On Mon, 13 Jul 2009 10:56:01 -0700, CG
wrote:

Here's an example:

Col A Col B Col C
corp stock $100
muni bond $200
corp corp $50
non-sec corp $150
UST bond $300

I want to sum the amounts in Col C, for which Col A, B, or A and B together
have the word "corp". So in this example, I would want to sum the amounts on
Lines 1, 3, and 4, to get a sum of $300. Since Line 3 has "corp" in both A
and B columns, I don't want the formula to count it twice.

"Francis" wrote:

Hi

Not very clear as to what you want to achieve, would you post an example
showing the before and after result?
Further, pls indicate if you are using Excel 2007 or earlier version

General formula to use for counting with more than more 2 criteria
are :
Prior to XL2007
1) SUMPRODUCT(--(Range1="Criteria1"),--(Range2="Criteria2")) or
2) SUM((Range1="Criteria1")*(Range2="Criteria2"))

XL2007

COUNTIFS(Range1,"Criteria1",Range2,"Criteria2")


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CG" wrote:

As a side note, the criteria I am using in the two columns are text rather
than numeric, if that makes a difference.

"CG" wrote:

I am trying to write a SUMIFs formula using two sets of criteria from
different columns, but I need it to be an "either/or" formula. Is there a
way to do this with SUMIFs, or should I use a different formula?

SUMIFS using an "Or" condition?
 

Excel 2007
Method #1 SUMIFS()
Method #2 Sumproduct (DeMorgan's Logic)
Method #3 Advanced Filter
http://www.mediafire.com/file/gjywmqn4nw4/07_13_09.xlsx