Wildcards in functions
 

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?

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)))

"oops" wrote:

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?

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!!

"Dave Breitenbach" wrote:

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)))

"oops" wrote:

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?

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!!

"Dave Breitenbach" wrote:
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)))
....

Change the formula above to

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

Thanks Harlan!!

"Harlan Grove" wrote:

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!!

"Dave Breitenbach" wrote:
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)))
....

Change the formula above to

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