Wildcards in functions

O

oops

I need to pick certain cells from a calendar, and add the numbers in those
cells. The problem I am running into is that my data is entered in random
cells, and in following format: I8, I3, I5. I need to find all the cells with
data beginning with the "I", and then add the numbers in the cells. from
above examples, it would be 8+3+5 for a total of 16. I have been trying the
countif function, but no luck yet. Any Ideas?
 
D

Dave Breitenbach

try this...(change m31:m33 to whatever range you'd like to cover)

=SUMPRODUCT(--(LEFT($M$31:$M$33,1)="I"),--(RIGHT($M$31:$M$33,LEN($M$31:$M$33)-1)))
 
O

oops

This formuls is working only when I have an "I" value in every cell of the
range. For ex., If I put I5 in each of the cells m31:m33, I get a "15", which
is correct. If I leave a cell blank, or with any other value than an I, I get
the dreaded #value! error. Any other ideas out there? I thank you for the
help so far!!
 
H

Harlan Grove

oops wrote...
This formuls is working only when I have an "I" value in every cell of the
range. For ex., If I put I5 in each of the cells m31:m33, I get a "15", which
is correct. If I leave a cell blank, or with any other value than an I, I get
the dreaded #value! error. Any other ideas out there? I thank you for the
help so far!!
Click to expand...
....

Change the formula above to

=SUMPRODUCT(--(LEFT($M$31:$M$33,1)="I"),--("0"&MID($M$31:$M$33,2,256)))
 
Top