Begins with as criteria in function
 

All,

I'm trying to do a sumif. For the criteria I want to sum anything that
begins with a value in cell A7. So my formula looks like
=sumif('Worksheet2,!$I$3:$I$586,A7*,'Worksheet2,!J 7:J590)
the value in cell A7 is 4040 SL

I've tried all sorts of variations for the A7* but it returns 0. If I
type in "4040 SL*" for the criteria it comes up with a pretty close
number but not the exact number.

Any ideas?

Jhorsley


--
jhorsley
------------------------------------------------------------------------
jhorsley's Profile: http://www.excelforum.com/member.php...o&userid=26040
View this thread: http://www.excelforum.com/showthread...hreadid=480623

Begins with as criteria in function
 

I believe your problem lies in the fact that your two ranges are not the
same size. Try this:

=sumif('Worksheet2,!$I$3:$I$586,A7,'Worksheet2,!J3 :J586)

You must start and end on the same rows, or your formula will be
skewed.

Does this work for you?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=480623

Begins with as criteria in function
 

Hi
Try
=sumif('Worksheet2,!$I$3:$I$586,"A7*",'Worksheet2, !J7:J590)

Regards

Roger Govier


jhorsley wrote:
All,

I'm trying to do a sumif. For the criteria I want to sum anything that
begins with a value in cell A7. So my formula looks like
=sumif('Worksheet2,!$I$3:$I$586,A7*,'Worksheet2,!J 7:J590)
the value in cell A7 is 4040 SL

I've tried all sorts of variations for the A7* but it returns 0. If I
type in "4040 SL*" for the criteria it comes up with a pretty close
number but not the exact number.

Any ideas?

Jhorsley

Begins with as criteria in function
 

"jhorsley" wrote...
I'm trying to do a sumif. For the criteria I want to sum anything that
begins with a value in cell A7. So my formula looks like
=sumif('Worksheet2,!$I$3:$I$586,A7*,'Worksheet2,! J7:J590)
the value in cell A7 is 4040 SL

I've tried all sorts of variations for the A7* but it returns 0. If I
type in "4040 SL*" for the criteria it comes up with a pretty close
number but not the exact number.

Did you try

=SUMIF('Worksheet2,!$I$3:$I$586,A7&"*",'Worksheet2 ,!J7:J590)

Begins with as criteria in function
 

=SUMIF(Worksheet2!$I$3:$I$586,A7&"*",Worksheet2!$J $3:$J$586)

jhorsley wrote:
All,

I'm trying to do a sumif. For the criteria I want to sum anything that
begins with a value in cell A7. So my formula looks like
=sumif('Worksheet2,!$I$3:$I$586,A7*,'Worksheet2,!J 7:J590)
the value in cell A7 is 4040 SL

I've tried all sorts of variations for the A7* but it returns 0. If I
type in "4040 SL*" for the criteria it comes up with a pretty close
number but not the exact number.

Any ideas?

Jhorsley

Begins with as criteria in function
 

I believe your problem lies in the fact that your two ranges are not the
same size and the wrong format for using the wildcard. Try this:

=sumif('Worksheet2,!$I$3:$I$586,A7&"*",'Worksheet2 ,!J3:J586)

You must start and end on the same rows, or your formula will be
skewed.
Your wildcard must be inside quotes and concatenated to the cell
reference with the ampersand '&'.

Does this work for you?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=480623

Begins with as criteria in function
 

Change your A7* to A7 & "*"

A7* = cell A7 multiplied by ????
A7 &"*" = A7's value & *


=sumif('Worksheet2,!$I$3:$I$586,A7&"*",'Worksheet2 ,!J7:J590)

Begins with as criteria in function
 

Sorry

Totally misread the question. I though you wanted to sum where the values in
column I contained "A7" plus following characters or numbers.
You have correct format from others.


Regards

Roger Govier


Roger Govier wrote:
Hi
Try
=sumif('Worksheet2,!$I$3:$I$586,"A7*",'Worksheet2, !J7:J590)

Regards

Roger Govier


jhorsley wrote:

All,

I'm trying to do a sumif. For the criteria I want to sum anything that
begins with a value in cell A7. So my formula looks like
=sumif('Worksheet2,!$I$3:$I$586,A7*,'Worksheet2,!J 7:J590)
the value in cell A7 is 4040 SL

I've tried all sorts of variations for the A7* but it returns 0. If I
type in "4040 SL*" for the criteria it comes up with a pretty close
number but not the exact number.
Any ideas?

Jhorsley

Begins with as criteria in function
 

Thanks a million. That was exactly what I needed.


--
jhorsley
------------------------------------------------------------------------
jhorsley's Profile: http://www.excelforum.com/member.php...o&userid=26040
View this thread: http://www.excelforum.com/showthread...hreadid=480623