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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|