A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sumproduct a column where 2 adj text columns contain same value



 
 
Thread Tools Display Modes
  #1  
Old March 5th, 2009, 03:29 AM posted to microsoft.public.excel.newusers
Struggling in Sheffield[_2_]
external usenet poster
 
Posts: 66
Default Sumproduct a column where 2 adj text columns contain same value

Hi,
I'm using a sumproduct formula to ascertain the number of times that a value
between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell in
column B (B3:B26) contains either "Smith", "Draper" or "Jones":

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={" Smith","Draper","Jones"}))

This formula works fine and details the number of entries where the value in
column A is between 0 and 1, and the name in the adjacent column B cell is
Smith Draper or Jones.

What I need to do is the same SUMPRODUCT of 0-1 entries in column A but this
time where the names listed in column B are equal to more names listed in
Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to my
SUMPRODUCT total (if the value in A7 is between 0 and 1).

Keep skirting around the edges of this one without being quite able to nail
it.
Any pointers gratefully received.
Cheers,
Steve.
  #2  
Old March 5th, 2009, 04:00 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default Sumproduct a column where 2 adj text columns contain same value

Use cells to hold the criteria** :

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(B3:B26=G3),--(C3:C26=G3))

** Use cells to hold the criteria:

You'd have to redo this formula since array constants can't use cell
references.

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={ "Smith","Draper","Jones"}))


=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
Hi,
I'm using a sumproduct formula to ascertain the number of times that a
value
between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell
in
column B (B3:B26) contains either "Smith", "Draper" or "Jones":

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={" Smith","Draper","Jones"}))

This formula works fine and details the number of entries where the value
in
column A is between 0 and 1, and the name in the adjacent column B cell is
Smith Draper or Jones.

What I need to do is the same SUMPRODUCT of 0-1 entries in column A but
this
time where the names listed in column B are equal to more names listed in
Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to
my
SUMPRODUCT total (if the value in A7 is between 0 and 1).

Keep skirting around the edges of this one without being quite able to
nail
it.
Any pointers gratefully received.
Cheers,
Steve.



  #3  
Old March 5th, 2009, 05:00 AM posted to microsoft.public.excel.newusers
Struggling in Sheffield[_2_]
external usenet poster
 
Posts: 66
Default Sumproduct a column where 2 adj text columns contain same valu

Hi,
I get the gist of what you're saying but can't see how B3:B26 is being
compared to C3:C26 for the three names?

A B C
3 0.12 Smith Jones
4 2.03 Draper Draper
5 0.65 Jones Jones
6 0.81 Smith Smith
7 0.33 Jones Draper
8 1.52 Smith Smith
9 0.74 Jones Jones
10 3.02 Draper Jones

For the table illustrated I need my formula to return the value of "3".

A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name "Jones"
(count 1)
A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name "Smith"
(count 2)
A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name "Jones"
(count 3)

Whilst names match on other rows or the value in column A is less than 1,
only three times do all these criteria line up and this is what I need to
count.
Cheers,
Steve.



"T. Valko" wrote:

Use cells to hold the criteria** :

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(B3:B26=G3),--(C3:C26=G3))

** Use cells to hold the criteria:

You'd have to redo this formula since array constants can't use cell
references.

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={ "Smith","Draper","Jones"}))


=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
Hi,
I'm using a sumproduct formula to ascertain the number of times that a
value
between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell
in
column B (B3:B26) contains either "Smith", "Draper" or "Jones":

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={" Smith","Draper","Jones"}))

This formula works fine and details the number of entries where the value
in
column A is between 0 and 1, and the name in the adjacent column B cell is
Smith Draper or Jones.

What I need to do is the same SUMPRODUCT of 0-1 entries in column A but
this
time where the names listed in column B are equal to more names listed in
Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to
my
SUMPRODUCT total (if the value in A7 is between 0 and 1).

Keep skirting around the edges of this one without being quite able to
nail
it.
Any pointers gratefully received.
Cheers,
Steve.




  #4  
Old March 5th, 2009, 05:41 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default Sumproduct a column where 2 adj text columns contain same valu

I misunderstood your requirement.

I thought you just wanted to match Jones and Jones.

Try this:

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
Hi,
I get the gist of what you're saying but can't see how B3:B26 is being
compared to C3:C26 for the three names?

A B C
3 0.12 Smith Jones
4 2.03 Draper Draper
5 0.65 Jones Jones
6 0.81 Smith Smith
7 0.33 Jones Draper
8 1.52 Smith Smith
9 0.74 Jones Jones
10 3.02 Draper Jones

For the table illustrated I need my formula to return the value of "3".

A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name
"Jones"
(count 1)
A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name
"Smith"
(count 2)
A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name
"Jones"
(count 3)

Whilst names match on other rows or the value in column A is less than 1,
only three times do all these criteria line up and this is what I need to
count.
Cheers,
Steve.



"T. Valko" wrote:

Use cells to hold the criteria** :

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(B3:B26=G3),--(C3:C26=G3))

** Use cells to hold the criteria:

You'd have to redo this formula since array constants can't use cell
references.

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={ "Smith","Draper","Jones"}))


=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"

wrote in message
...
Hi,
I'm using a sumproduct formula to ascertain the number of times that a
value
between 0 and 1 occurs in column A (range A3:A26), where the adjacent
cell
in
column B (B3:B26) contains either "Smith", "Draper" or "Jones":

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={" Smith","Draper","Jones"}))

This formula works fine and details the number of entries where the
value
in
column A is between 0 and 1, and the name in the adjacent column B cell
is
Smith Draper or Jones.

What I need to do is the same SUMPRODUCT of 0-1 entries in column A but
this
time where the names listed in column B are equal to more names listed
in
Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added
to
my
SUMPRODUCT total (if the value in A7 is between 0 and 1).

Keep skirting around the edges of this one without being quite able to
nail
it.
Any pointers gratefully received.
Cheers,
Steve.






  #5  
Old March 5th, 2009, 01:28 PM posted to microsoft.public.excel.newusers
Struggling in Sheffield[_2_]
external usenet poster
 
Posts: 66
Default Sumproduct a column where 2 adj text columns contain same valu

Hi Biff,
Works a treat, many thanks.

"T. Valko" wrote:

I misunderstood your requirement.

I thought you just wanted to match Jones and Jones.

Try this:

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
Hi,
I get the gist of what you're saying but can't see how B3:B26 is being
compared to C3:C26 for the three names?

A B C
3 0.12 Smith Jones
4 2.03 Draper Draper
5 0.65 Jones Jones
6 0.81 Smith Smith
7 0.33 Jones Draper
8 1.52 Smith Smith
9 0.74 Jones Jones
10 3.02 Draper Jones

For the table illustrated I need my formula to return the value of "3".

A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name
"Jones"
(count 1)
A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name
"Smith"
(count 2)
A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name
"Jones"
(count 3)

Whilst names match on other rows or the value in column A is less than 1,
only three times do all these criteria line up and this is what I need to
count.
Cheers,
Steve.



"T. Valko" wrote:

Use cells to hold the criteria** :

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(B3:B26=G3),--(C3:C26=G3))

** Use cells to hold the criteria:

You'd have to redo this formula since array constants can't use cell
references.

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={ "Smith","Draper","Jones"}))

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"

wrote in message
...
Hi,
I'm using a sumproduct formula to ascertain the number of times that a
value
between 0 and 1 occurs in column A (range A3:A26), where the adjacent
cell
in
column B (B3:B26) contains either "Smith", "Draper" or "Jones":

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={" Smith","Draper","Jones"}))

This formula works fine and details the number of entries where the
value
in
column A is between 0 and 1, and the name in the adjacent column B cell
is
Smith Draper or Jones.

What I need to do is the same SUMPRODUCT of 0-1 entries in column A but
this
time where the names listed in column B are equal to more names listed
in
Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added
to
my
SUMPRODUCT total (if the value in A7 is between 0 and 1).

Keep skirting around the edges of this one without being quite able to
nail
it.
Any pointers gratefully received.
Cheers,
Steve.






  #6  
Old March 5th, 2009, 03:36 PM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default Sumproduct a column where 2 adj text columns contain same valu

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
Hi Biff,
Works a treat, many thanks.

"T. Valko" wrote:

I misunderstood your requirement.

I thought you just wanted to match Jones and Jones.

Try this:

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"

wrote in message
...
Hi,
I get the gist of what you're saying but can't see how B3:B26 is being
compared to C3:C26 for the three names?

A B C
3 0.12 Smith Jones
4 2.03 Draper Draper
5 0.65 Jones Jones
6 0.81 Smith Smith
7 0.33 Jones Draper
8 1.52 Smith Smith
9 0.74 Jones Jones
10 3.02 Draper Jones

For the table illustrated I need my formula to return the value of "3".

A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name
"Jones"
(count 1)
A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name
"Smith"
(count 2)
A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name
"Jones"
(count 3)

Whilst names match on other rows or the value in column A is less than
1,
only three times do all these criteria line up and this is what I need
to
count.
Cheers,
Steve.



"T. Valko" wrote:

Use cells to hold the criteria** :

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(B3:B26=G3),--(C3:C26=G3))

** Use cells to hold the criteria:

You'd have to redo this formula since array constants can't use cell
references.

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={ "Smith","Draper","Jones"}))

=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"

wrote in message
...
Hi,
I'm using a sumproduct formula to ascertain the number of times that
a
value
between 0 and 1 occurs in column A (range A3:A26), where the
adjacent
cell
in
column B (B3:B26) contains either "Smith", "Draper" or "Jones":

=SUMPRODUCT((A3:A260.00)*(A3:A261.01)*(B3:B26={" Smith","Draper","Jones"}))

This formula works fine and details the number of entries where the
value
in
column A is between 0 and 1, and the name in the adjacent column B
cell
is
Smith Draper or Jones.

What I need to do is the same SUMPRODUCT of 0-1 entries in column A
but
this
time where the names listed in column B are equal to more names
listed
in
Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the
added
to
my
SUMPRODUCT total (if the value in A7 is between 0 and 1).

Keep skirting around the edges of this one without being quite able
to
nail
it.
Any pointers gratefully received.
Cheers,
Steve.








 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.