SUMIFS using an "Or" condition?

[CG]

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?

 

[CG]

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

 

[CG]

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.

 

[Francis]

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

 

[Lars-Åke Aspelin]

Try this formula:

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

Hope this helps / Lars-Åke

 

[Bob Phillips]

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

 

[CG]

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.

 

[CG]

I am using XL 2007

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

 

[Lars-Åke Aspelin]

Try this:

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

Hope this helps / Lars-Åke

 

[CG]

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]

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

Lars-Åke

 

[Lars-Åke Aspelin]

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",B1:B10)),C1:C10)

Hope this helps / Lars-Åke

 

[CG]

Thank you so much. This has been very helpful.

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",B1:B10)),C1:C10)

Hope this helps / Lars-Ã…ke

 

[Herbert Seidenberg]

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