Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcards in SUMPRODUCT
Can anyone help with this please?
I have 3 columns of data and wish to analyse the number of occasions on which criteria in Columns B and C are met. For example: - Column B lists types of vehicle - Column C lists whether or not the vehicle is roadworthy The complicating issue (from my point of view) is that I need to include specific words within cells in Column B within the count, ie; types of vehicles in B will be, for example, "Leyland Olympians", "Volvo Olympians", etc. In the calculation I need the SUMPRODUCT result to show not only the number of cases where Leyland Olympians and Volvo Olympians are roadworthy (that's easy enough) but when all Olympians are rodaworthy. I have simplified the example - I don't want to merely add the 2 figures together as there are many different variables beyond the 2 I have listed. I have tried: =SUMPRODUCT(($B$2:$B$14="*Olympian*")*($C$2:$C$14= "R")) but this doesn't return the correct result. It seems that COUNTIF can handle wildcards but SUMPRODUCT can't? Any suggestions would be welcomed! Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcards in SUMPRODUCT
=SUMPRODUCT(--isnumber(search("Olympian",$B$2:$B$14)),--($C$2:$C$14="R"))
=find() is case sensitive. =search() is not case sensitive. Terry Bennett wrote: Can anyone help with this please? I have 3 columns of data and wish to analyse the number of occasions on which criteria in Columns B and C are met. For example: - Column B lists types of vehicle - Column C lists whether or not the vehicle is roadworthy The complicating issue (from my point of view) is that I need to include specific words within cells in Column B within the count, ie; types of vehicles in B will be, for example, "Leyland Olympians", "Volvo Olympians", etc. In the calculation I need the SUMPRODUCT result to show not only the number of cases where Leyland Olympians and Volvo Olympians are roadworthy (that's easy enough) but when all Olympians are rodaworthy. I have simplified the example - I don't want to merely add the 2 figures together as there are many different variables beyond the 2 I have listed. I have tried: =SUMPRODUCT(($B$2:$B$14="*Olympian*")*($C$2:$C$14= "R")) but this doesn't return the correct result. It seems that COUNTIF can handle wildcards but SUMPRODUCT can't? Any suggestions would be welcomed! Many thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcards in SUMPRODUCT
Hi,
A small variant of the SUMPRODUCT() function suggested by Dave SUMPRODUCT(ISNUMBER(SEARCH("Olympian",A2:A6,1))*(B 2:B6="Y")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dave Peterson" wrote in message ... =SUMPRODUCT(--isnumber(search("Olympian",$B$2:$B$14)),--($C$2:$C$14="R")) =find() is case sensitive. =search() is not case sensitive. Terry Bennett wrote: Can anyone help with this please? I have 3 columns of data and wish to analyse the number of occasions on which criteria in Columns B and C are met. For example: - Column B lists types of vehicle - Column C lists whether or not the vehicle is roadworthy The complicating issue (from my point of view) is that I need to include specific words within cells in Column B within the count, ie; types of vehicles in B will be, for example, "Leyland Olympians", "Volvo Olympians", etc. In the calculation I need the SUMPRODUCT result to show not only the number of cases where Leyland Olympians and Volvo Olympians are roadworthy (that's easy enough) but when all Olympians are rodaworthy. I have simplified the example - I don't want to merely add the 2 figures together as there are many different variables beyond the 2 I have listed. I have tried: =SUMPRODUCT(($B$2:$B$14="*Olympian*")*($C$2:$C$14= "R")) but this doesn't return the correct result. It seems that COUNTIF can handle wildcards but SUMPRODUCT can't? Any suggestions would be welcomed! Many thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcards in SUMPRODUCT
Many thanks guys
"Dave Peterson" wrote in message ... =SUMPRODUCT(--isnumber(search("Olympian",$B$2:$B$14)),--($C$2:$C$14="R")) =find() is case sensitive. =search() is not case sensitive. Terry Bennett wrote: Can anyone help with this please? I have 3 columns of data and wish to analyse the number of occasions on which criteria in Columns B and C are met. For example: - Column B lists types of vehicle - Column C lists whether or not the vehicle is roadworthy The complicating issue (from my point of view) is that I need to include specific words within cells in Column B within the count, ie; types of vehicles in B will be, for example, "Leyland Olympians", "Volvo Olympians", etc. In the calculation I need the SUMPRODUCT result to show not only the number of cases where Leyland Olympians and Volvo Olympians are roadworthy (that's easy enough) but when all Olympians are rodaworthy. I have simplified the example - I don't want to merely add the 2 figures together as there are many different variables beyond the 2 I have listed. I have tried: =SUMPRODUCT(($B$2:$B$14="*Olympian*")*($C$2:$C$14= "R")) but this doesn't return the correct result. It seems that COUNTIF can handle wildcards but SUMPRODUCT can't? Any suggestions would be welcomed! Many thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcards with Sumproduct? | Excel Discussion (Misc queries) | |||
Sumproduct with wildcards | Excel Worksheet Functions | |||
sumproduct won't accept wildcards | Excel Worksheet Functions | |||
Sumproduct and wildcards | Excel Worksheet Functions | |||
Wildcards with SumProduct | Excel Worksheet Functions |