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?

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?

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?

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?