Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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:A26<1.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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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:A26<F3),--(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:A26<1.01)*(B3:B26={ "Smith","Draper","Jones"})) =SUMPRODUCT(--(A3:A26E3),--(A3:A26<F3),--(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:A26<1.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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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:A26<F3),--(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:A26<1.01)*(B3:B26={ "Smith","Draper","Jones"})) =SUMPRODUCT(--(A3:A26E3),--(A3:A26<F3),--(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:A26<1.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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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:A26<F3),--(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:A26<F3),--(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:A26<1.01)*(B3:B26={ "Smith","Draper","Jones"})) =SUMPRODUCT(--(A3:A26E3),--(A3:A26<F3),--(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:A26<1.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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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:A26<F3),--(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:A26<F3),--(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:A26<1.01)*(B3:B26={ "Smith","Draper","Jones"})) =SUMPRODUCT(--(A3:A26E3),--(A3:A26<F3),--(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:A26<1.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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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:A26<F3),--(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:A26<F3),--(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:A26<1.01)*(B3:B26={ "Smith","Draper","Jones"})) =SUMPRODUCT(--(A3:A26E3),--(A3:A26<F3),--(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:A26<1.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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t | Excel Worksheet Functions | |||
sumproduct 2 columns based on criteria in 3rd column | Excel Discussion (Misc queries) | |||
Sumproduct if column begins with text | Excel Worksheet Functions | |||
text from one column into multiple columns | Excel Discussion (Misc queries) | |||
Splitting text in one column into two (or more) columns. | Excel Worksheet Functions |