SEARCH IN SUMPRODUCT

[Lea from CA]

I am trying to sum column D where column A starts with "61", column B is
either ABC or DEF and column C = North. The result should be 2000 but I am
only getting 1500. The formula is excluding "6161" but I want that included.

What do I need to do to fis it? Thank you any assistance!


=SUMPRODUCT((Sheet2!$C$1:$C$15="NORTH")
*(ISNUMBER(SEARCH("61",Sheet2!$A$1:$A$15)))
*(Sheet2!$B$1:$B$15={"ABC","DEF"})*(Sheet2!$D$1:$D$15))
-SUMPRODUCT((Sheet2!$C$1:$C$15="NORTH")
*(ISNUMBER(SEARCH("?61",Sheet2!$A$1:$A$15)))
*(Sheet2!$B$1:$B$15={"ABC","DEF"})*(Sheet2!$D$1:$D$15))


A B C D
1 6100 ABC NORTH 1000
2 6100 DEF NORTH 500
3 6100 GHI SOUTH 1000
4 6100 JKL EAST 500
5 6100 MNO WEST 1000
6 6161 ABC NORTH 500
7 6200 ABC SOUTH 1000
8 6200 DEF EAST 500
9 6200 GHI WEST 1000
10 6200 JKL NORTH 500
11 6261 ABC NORTH 1000
12 6660 ABC SOUTH 500
13 6660 DEF EAST 1000
14 6661 ABC NORTH 500
15 6661 DEF NORTH 1000

 

[Cordell]

Try this:

{=SUMPRODUCT(--(LEFT(A1:A15,2)="61"),--(B1:B15="abc")+--(B1:B15="def"),--(C1:C15="NORTH"),D115)}

 

[Pivot Man]

I am sure there is a more sophisticated way...but here is my solution. See
formulae at the bottom

A B C D
1 6100 ABC NORTH 1000 1
2 6100 DEF NORTH 500 1
3 6100 GHI SOUTH 1000 0
4 6100 JKL EAST 500 0
5 6100 MNO WEST 1000 0
6 6161 ABC NORTH 500 1
7 6200 ABC SOUTH 1000 0
8 6200 DEF EAST 500 0
9 6200 GHI WEST 1000 0
10 6200 JKL NORTH 500 0
11 6261 ABC NORTH 1000 0
12 6660 ABC SOUTH 500 0
13 6660 DEF EAST 1000 0
14 6661 ABC NORTH 500 0
15 6661 DEF NORTH 1000 0
2000


column f =IF(AND(LEFT(B2,2)="61",OR(C2="ABC",C2="DEF"),D2="North"),1,0)
e17 =SUMIF(F2:F16,1,E2:E16)

 

[John C]

=SUMPRODUCT(--(LEFT($A$1:$A$15,2)="61"),--($B$1:$B$15="ABC")+($B$1:$B$15="DEF"),--($C$1:$C$15="NORTH"),($D$1:$D$15))

Hope this helps!

 

[Lars-Åke Aspelin]

I am trying to sum column D where column A starts with "61", column B is
either ABC or DEF and column C = North. The result should be 2000 but I am
only getting 1500. The formula is excluding "6161" but I want that included.

What do I need to do to fis it? Thank you any assistance!


=SUMPRODUCT((Sheet2!$C$1:$C$15="NORTH")
*(ISNUMBER(SEARCH("61",Sheet2!$A$1:$A$15)))
*(Sheet2!$B$1:$B$15={"ABC","DEF"})*(Sheet2!$D$1:$D$15))
-SUMPRODUCT((Sheet2!$C$1:$C$15="NORTH")
*(ISNUMBER(SEARCH("?61",Sheet2!$A$1:$A$15)))
*(Sheet2!$B$1:$B$15={"ABC","DEF"})*(Sheet2!$D$1:$D$15))


A B C D
1 6100 ABC NORTH 1000
2 6100 DEF NORTH 500
3 6100 GHI SOUTH 1000
4 6100 JKL EAST 500
5 6100 MNO WEST 1000
6 6161 ABC NORTH 500
7 6200 ABC SOUTH 1000
8 6200 DEF EAST 500
9 6200 GHI WEST 1000
10 6200 JKL NORTH 500
11 6261 ABC NORTH 1000
12 6660 ABC SOUTH 500
13 6660 DEF EAST 1000
14 6661 ABC NORTH 500
15 6661 DEF NORTH 1000

The reason why your formula does not work is that you

firstly include rows that have "61" anywhere and
secondly excludes rows that have "61" anywhere but first.

That means that "6161" is not counted as it is both included and
excluded. (It is not included twice).

You have already got some suggestions for alternative formulas
including LEFT sort find if "61" is in the beginning.

Hope this helps / Lars-Åke

 

[Lea from CA]

You guys are quick in responding! Thank you! It worked.

 

[Brian]

Is there a way to modify this formula to exclude certain criteria?
For example....suppose I have a column E that has interest rates in it, and
I want to obtain the weighted average yield, but don't want to incorporate
anything of the data if the words in column B that have a "C" in it.
I know how to write a formula to limit the reponse to those with "C" in it
by using the ISNUMBER(SEARCH()) function....but how do I do the reverse where
I'd like to get the weighted average by excluding this data?

thank you

 

[Lars-Åke Aspelin]

If you have a working formula with ISNUMBER(SEARCH())
to limit the response to those with a "C" in them,
try your formula with NOT(ISNUMBER(SEARCH())).

Hope this helps / Lars-Åke

 

[Brian]

that did it...thank you!

If you have a working formula with ISNUMBER(SEARCH())
to limit the response to those with a "C" in them,
try your formula with NOT(ISNUMBER(SEARCH())).

Hope this helps / Lars-Ã…ke




.