SumIfs(or)?

[Reddog94]

Can an OR function be imbedded in a SumIfs function? For example, I want to do a SumIfs with two criteria, however the second criteria could be 3 different values.

For example,

Column 1 = department number
Column 2 = expense category
Column 3 = data to be summed

I want to sum all data in column 3 if column 1 = "1234" and column 2 = either "labor", "fringe", or "merit".

Using Excel 2007.
Thank you.

 

[T. Valko]

Can an OR function be imbedded in a SumIfs function? For example, I want to do a SumIfs with two criteria, however the second criteria could be 3 different values.

For example,

Column 1 = department number
Column 2 = expense category
Column 3 = data to be summed

I want to sum all data in column 3 if column 1 = "1234" and column 2 = either "labor", "fringe", or "merit".

Using Excel 2007.
Thank you.

Try this...

=SUM(SUMIFS(C1:C10,A1:A10,1234,B1:B10,{"labor","fringe","merit"}))

 

[taurean]

This will work:
=SUMPRODUCT((C2:C10)*(A2:A10=1234)*ISNUMBER(SEARCH({"labor","fringe","merit"},B2:B10,1)))

 

[Reddog94]

Yes - awesome, thank you! I really need to spend some time discoving the value of array formulas...

 

[Reddog94]

To clarify, I used the first solution posted by Biff. The second solution posted worked too, however I tend to avoid SumProduct formulas as I find they bog down my files. Thanks to both of you!

 

[taurean]

To clarify, I used the first solution posted by Biff. The second solution posted worked too, however I tend to avoid SumProduct formulas as I find they bog down my files. Thanks to both of you!

Yes, I have experienced this too and read sometimes here as well but haven't got any clue as why do they tend to be slow .

It was just that I had worked it out so decided to post it anyway. Thank you for your feedback.

 

[T. Valko]

To clarify, I used the first solution posted by Biff. The second solution posted worked too, however I tend to avoid SumProduct formulas as I find they bog down my files. Thanks to both of you!

You're welcome. We appreciate the feedback!

 

[mousepad]

Can cell references and wildcards be used in the array? I'm trying to do SUMIFS if a column of text contains the full word only. For example, finding "ship" in these cells:

"cruise ship" = TRUE
"spaceship" = FALSE
"shipping" = FALSE

=SUM(SUMIFS(sum_range, criteria_range, {"* ship *", "* ship", "ship *"}))
Works, but I'm looking for a dynamic version.

=SUM(SUMIFS(sum_range, criteria_range, {"* "&A1&" *", "* "&A1, A1&" *"}))
Causes a formula error.

 

[T. Valko]

Can cell references and wildcards be used in the array? I'm trying to do SUMIFS if a column of text contains the full word only. For example, finding "ship" in these cells:

"cruise ship" = TRUE
"spaceship" = FALSE
"shipping" = FALSE

=SUM(SUMIFS(sum_range, criteria_range, {"* ship *", "* ship", "ship *"}))
Works, but I'm looking for a dynamic version.

=SUM(SUMIFS(sum_range, criteria_range, {"* "&A1&" *", "* "&A1, A1&" *"}))
Causes a formula error.

No, you can't use cell references in an array constant.

Try it like this:

Book1
	A	B	C	D
1	Ship	_	Ship	77
2	214	_	Shipment	80
3	_	_	_	17
4	_	_	shipping	34
5	_	_	spaceship	88
6	_	_	cruise ship	86
7	_	_	Robert Shipley	16
8	_	_	Ship Ahoy	51
9	_	_	Text	93
Sheet1

This formula entered in A2:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&A1&" "," "&C1:C9&" ")),D1:D9)

 

[mousepad]

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&A1&" "," "&C1:C9&" ")),D1:D9)

" "&A1&" "
How is the criteria above picking up "ship x" "x ship" and " ship "? The way it's written seems like only " ship " would calculate.

" "&C1:C9&" "
Why are the space appendings necessary for the search range?

Why is -- necessary?

Exactly what I need. Thank you Biff!