Using Wildcard characters in sumproduct

[Guest]

=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?

 

[Biff]

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

 

[Guest]

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.

 

[R.VENKATARAMAN]

I saw a message in daily dose of excel(Rob ven Gelder)

click this url
http://www.dicks-blog.com/archives/category/worksheet-functions/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