ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   SEARCH IN SUMPRODUCT (https://www.excelbanter.com/excel-discussion-misc-queries/196340-search-sumproduct.html)

Lea from CA[_2_]
SEARCH IN SUMPRODUCT
 
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
SEARCH IN SUMPRODUCT
 
Try this:

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

--
Cordell


"Lea from CA" wrote:

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

Pivot Man[_2_]
SEARCH IN SUMPRODUCT
 
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)


"Lea from CA" wrote:

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

John C[_2_]
SEARCH IN SUMPRODUCT
 
=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!
--
John C


"Lea from CA" wrote:

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

Lars-Åke Aspelin[_2_]
SEARCH IN SUMPRODUCT
 
On Fri, 25 Jul 2008 11:22:40 -0700, Lea from CA
wrote:

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[_2_]
SEARCH IN SUMPRODUCT
 
You guys are quick in responding! Thank you! It worked.


"Cordell" wrote:

Try this:

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

--
Cordell


"Lea from CA" wrote:

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

Brian
SEARCH IN SUMPRODUCT
 
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" wrote:

On Fri, 25 Jul 2008 11:22:40 -0700, Lea from CA
wrote:

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

Lars-Åke Aspelin[_2_]
SEARCH IN SUMPRODUCT
 
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


On Thu, 3 Dec 2009 10:44:02 -0800, Brian
wrote:

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" wrote:

On Fri, 25 Jul 2008 11:22:40 -0700, Lea from CA
wrote:

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

Brian
SEARCH IN SUMPRODUCT
 
that did it...thank you!

"Lars-Ã…ke Aspelin" wrote:

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


On Thu, 3 Dec 2009 10:44:02 -0800, Brian
wrote:

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" wrote:

On Fri, 25 Jul 2008 11:22:40 -0700, Lea from CA
wrote:

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



.


All times are GMT +1. The time now is 09:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com