Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Wildcard characters in sumproduct
=SUMPRODUCT(('Jan 05'!$B$2:$B$4963="20 ")*('Jan
05'!$C$2:$C$4963="H*")*'Jan 05'!$D$2:$D$4963) In above example I would like to sum certain celss if c2 through c4963 has a string that starts with an H and I dont care what follows. I used the * as wildcard but I don't think it is right as it does not work. How do you do this? |
#2
|
|||
|
|||
Hi!
Sumproduct itself won't accept wildcards. Try this: =SUMPRODUCT(('Jan 05'!$B$2:$B$4963="20 ")*(LEFT('Jan 05'!$C$2:$C$4963)="H")*'Jan 05'!$D$2:$D$4963) Biff "Charles" wrote in message ... =SUMPRODUCT(('Jan 05'!$B$2:$B$4963="20 ")*('Jan 05'!$C$2:$C$4963="H*")*'Jan 05'!$D$2:$D$4963) In above example I would like to sum certain celss if c2 through c4963 has a string that starts with an H and I dont care what follows. I used the * as wildcard but I don't think it is right as it does not work. How do you do this? |
#3
|
|||
|
|||
It may not be the neatest but...
Make another column, and use an IF statement to make one of the columns your using in the SUMPRODUCT = 0 unless the first character = H. I think you'd use the LEFT function to obtain the H or whatever else it is, to use in the IF function. Eg. =IF(LEFT(C1,1)="h",A1,0) Then you can use this new column instead of one of the older ones in your SUMPRODUCT function. "Charles" wrote: =SUMPRODUCT(('Jan 05'!$B$2:$B$4963="20 ")*('Jan 05'!$C$2:$C$4963="H*")*'Jan 05'!$D$2:$D$4963) In above example I would like to sum certain celss if c2 through c4963 has a string that starts with an H and I dont care what follows. I used the * as wildcard but I don't think it is right as it does not work. How do you do this? |
#4
|
|||
|
|||
I saw a message in daily dose of excel(Rob ven Gelder)
click this url http://www.dicks-blog.com/archives/c...s/counting-and -summing-functions/ among the recent posts on the left side of the page choose Multicolumn Sum with Wildcard I am sure this will solve your problem Charles wrote in message ... =SUMPRODUCT(('Jan 05'!$B$2:$B$4963="20 ")*('Jan 05'!$C$2:$C$4963="H*")*'Jan 05'!$D$2:$D$4963) In above example I would like to sum certain celss if c2 through c4963 has a string that starts with an H and I dont care what follows. I used the * as wildcard but I don't think it is right as it does not work. How do you do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with Wildcard | Excel Worksheet Functions | |||
Sumproduct Wildcard | Excel Discussion (Misc queries) | |||
Sumproduct with Wildcard * | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |