SUMIF formula help
 

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

SUMIF formula help
 

Perhaps because you're dividing the SUMIF() result by A9, which
ostensibly is part of your indicators, rather than the number of stores?

In article ,
Jane wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

SUMIF formula help
 

The formula does appear to be written correctly. How is it not working? Are
you receiving and error? Do you get the wrong result?

Without knowing more about your data, the results you're getting, and the
results you're expecting, the only thing I can suggest is to make sure that
your data in X5:X500 and A9 are all in fact numbers, and not text or numbers
stored as text.

HTH,
Elkar



"Jane" wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

SUMIF formula help
 

On Fri, 17 Aug 2007 10:34:01 -0700, Jane
wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

PRobably because your data is different.

For example, in the second sheet, you are dividing by A9. Is that what you
really want to do? Is col A still your indicators on this different sheet?
--ron

SUMIF formula help
 

I receive a "0" result

sample data:

col A col X A9 - 4
HISPANIC $45,093
HISPANIC $23,237
BLENDED $23409
AFR AMRCN $68450
BLENDED $19840
OTHER $75483
OTHER $09345

The sales data is actually a result of vlookup so I did a copy\ paste
special value and change the format from currency to number

what else should I cansider?

"Elkar" wrote:

The formula does appear to be written correctly. How is it not working? Are
you receiving and error? Do you get the wrong result?

Without knowing more about your data, the results you're getting, and the
results you're expecting, the only thing I can suggest is to make sure that
your data in X5:X500 and A9 are all in fact numbers, and not text or numbers
stored as text.

HTH,
Elkar



"Jane" wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

SUMIF formula help
 

HI there,
I should have changed my example to read that the A9 reference is actually
A3 and not part of my indicators.

thanks for that call-out - I can see where that would be a problem if it
were actually included in my indcators. SOrry for the confusion on that typo.

"JE McGimpsey" wrote:

Perhaps because you're dividing the SUMIF() result by A9, which
ostensibly is part of your indicators, rather than the number of stores?

In article ,
Jane wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

SUMIF formula help
 

I still suspect that column X is text, and not numbers. If data is already
entered as text, then simply changing the cell format to number won't convert
that data. Try one of these options:

If you still have VLOOKUP formulas in X5:X500, try enclosing them in a
VALUE() funcion: =VALUE(your vlookup here)

If you've gotten rid of the VLOOKUP formulas and just have data, then:

Enter a 1 into any blank cell
Copy that cell
Select your range X5:X500
Paste Special
Check the "Multiply" option
Click OK
Delete the 1 you entered originally

Now, all data in column X that can be recognized as a number will be
converted to a number.

Does that fix it?

HTH,
Elkar


"Jane" wrote:

I receive a "0" result

sample data:

col A col X A9 - 4
HISPANIC $45,093
HISPANIC $23,237
BLENDED $23409
AFR AMRCN $68450
BLENDED $19840
OTHER $75483
OTHER $09345

The sales data is actually a result of vlookup so I did a copy\ paste
special value and change the format from currency to number

what else should I cansider?

"Elkar" wrote:

The formula does appear to be written correctly. How is it not working? Are
you receiving and error? Do you get the wrong result?

Without knowing more about your data, the results you're getting, and the
results you're expecting, the only thing I can suggest is to make sure that
your data in X5:X500 and A9 are all in fact numbers, and not text or numbers
stored as text.

HTH,
Elkar



"Jane" wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

SUMIF formula help
 

Is the data actually BLENDED, no leading/trailing spaces?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jane" wrote in message
...
I receive a "0" result

sample data:

col A col X A9 - 4
HISPANIC $45,093
HISPANIC $23,237
BLENDED $23409
AFR AMRCN $68450
BLENDED $19840
OTHER $75483
OTHER $09345

The sales data is actually a result of vlookup so I did a copy\ paste
special value and change the format from currency to number

what else should I cansider?

"Elkar" wrote:

The formula does appear to be written correctly. How is it not working?
Are
you receiving and error? Do you get the wrong result?

Without knowing more about your data, the results you're getting, and the
results you're expecting, the only thing I can suggest is to make sure
that
your data in X5:X500 and A9 are all in fact numbers, and not text or
numbers
stored as text.

HTH,
Elkar



"Jane" wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my
# of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

SUMIF formula help
 

forgive my typo on that - I intended to say that my # of stores was in a cell
outside of my indicators - sorry for that confusion.

I've tried the suggestions frmo other s with no luck although I am filing
the suggestions for use in other instances if needed so I have learned a few
more things so happy with that :)

any other thoughts?

thanks, jane

"Ron Rosenfeld" wrote:

On Fri, 17 Aug 2007 10:34:01 -0700, Jane
wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

PRobably because your data is different.

For example, in the second sheet, you are dividing by A9. Is that what you
really want to do? Is col A still your indicators on this different sheet?
--ron

SUMIF formula help
 

HI Bob,
The "blended" was exported into excel so I:

1. went to the beginning and end of the work and backspaced/ deleted
acocrdingly and no spaces involved.

2. formatted as text as it formatted as 'general'

3. highlighted and did "text to columns" in the case it exported over as an
object.

No luck on any of it.

other thoughts I can try?

thank you, Jane
"Bob Phillips" wrote:

Is the data actually BLENDED, no leading/trailing spaces?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jane" wrote in message
...
I receive a "0" result

sample data:

col A col X A9 - 4
HISPANIC $45,093
HISPANIC $23,237
BLENDED $23409
AFR AMRCN $68450
BLENDED $19840
OTHER $75483
OTHER $09345

The sales data is actually a result of vlookup so I did a copy\ paste
special value and change the format from currency to number

what else should I cansider?

"Elkar" wrote:

The formula does appear to be written correctly. How is it not working?
Are
you receiving and error? Do you get the wrong result?

Without knowing more about your data, the results you're getting, and the
results you're expecting, the only thing I can suggest is to make sure
that
your data in X5:X500 and A9 are all in fact numbers, and not text or
numbers
stored as text.

HTH,
Elkar



"Jane" wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my
# of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

SUMIF formula help
 

HI there,
I tried both ideas with no luck but learned a few things I may be able to
use in other instances so thank you for that :)

I replied to Bonb Phillips this thoughts - unfortunately have had no
luck yet.

any other possibilities?
jane

"Elkar" wrote:

I still suspect that column X is text, and not numbers. If data is already
entered as text, then simply changing the cell format to number won't convert
that data. Try one of these options:

If you still have VLOOKUP formulas in X5:X500, try enclosing them in a
VALUE() funcion: =VALUE(your vlookup here)

If you've gotten rid of the VLOOKUP formulas and just have data, then:

Enter a 1 into any blank cell
Copy that cell
Select your range X5:X500
Paste Special
Check the "Multiply" option
Click OK
Delete the 1 you entered originally

Now, all data in column X that can be recognized as a number will be
converted to a number.

Does that fix it?

HTH,
Elkar


"Jane" wrote:

I receive a "0" result

sample data:

col A col X A9 - 4
HISPANIC $45,093
HISPANIC $23,237
BLENDED $23409
AFR AMRCN $68450
BLENDED $19840
OTHER $75483
OTHER $09345

The sales data is actually a result of vlookup so I did a copy\ paste
special value and change the format from currency to number

what else should I cansider?

"Elkar" wrote:

The formula does appear to be written correctly. How is it not working? Are
you receiving and error? Do you get the wrong result?

Without knowing more about your data, the results you're getting, and the
results you're expecting, the only thing I can suggest is to make sure that
your data in X5:X500 and A9 are all in fact numbers, and not text or numbers
stored as text.

HTH,
Elkar



"Jane" wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

SUMIF formula help
 

Eureka! I found a related posting and did this:
- replaced "BLENDED" w/ D3 where I placed the word BLENDED

=SUMIF($D$12:$D$45,$D$3,$F$12:$F$45)/D4

Thanks all for your ideas!

take good care

"Jane" wrote:

forgive my typo on that - I intended to say that my # of stores was in a cell
outside of my indicators - sorry for that confusion.

I've tried the suggestions frmo other s with no luck although I am filing
the suggestions for use in other instances if needed so I have learned a few
more things so happy with that :)

any other thoughts?

thanks, jane

"Ron Rosenfeld" wrote:

On Fri, 17 Aug 2007 10:34:01 -0700, Jane
wrote:

I have used this formula before with success to get an average - (col A
includes my indicators; col R includes my sales data; col F includes my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

PRobably because your data is different.

For example, in the second sheet, you are dividing by A9. Is that what you
really want to do? Is col A still your indicators on this different sheet?
--ron

SUMIF formula help
 

You probably had extra spaces, if you do this

=LEN(D3)

what result do you get?


=SUMIF($D$12:$D$45,"*BLENDED*",$F$12:$F$45)/D4

would work if you have extra spaces/invisible characters


--
Regards,

Peo Sjoblom


"Jane" wrote in message
...
Eureka! I found a related posting and did this:
- replaced "BLENDED" w/ D3 where I placed the word BLENDED

=SUMIF($D$12:$D$45,$D$3,$F$12:$F$45)/D4

Thanks all for your ideas!

take good care

"Jane" wrote:

forgive my typo on that - I intended to say that my # of stores was in a
cell
outside of my indicators - sorry for that confusion.

I've tried the suggestions frmo other s with no luck although I am filing
the suggestions for use in other instances if needed so I have learned a
few
more things so happy with that :)

any other thoughts?

thanks, jane

"Ron Rosenfeld" wrote:

On Fri, 17 Aug 2007 10:34:01 -0700, Jane

wrote:

I have used this formula before with success to get an average - (col
A
includes my indicators; col R includes my sales data; col F includes
my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

PRobably because your data is different.

For example, in the second sheet, you are dividing by A9. Is that what
you
really want to do? Is col A still your indicators on this different
sheet?
--ron

SUMIF formula help
 

I checked for extra spaces - but will try using your method and will report
back

thanks! jane

"Peo Sjoblom" wrote:

You probably had extra spaces, if you do this

=LEN(D3)

what result do you get?


=SUMIF($D$12:$D$45,"*BLENDED*",$F$12:$F$45)/D4

would work if you have extra spaces/invisible characters


--
Regards,

Peo Sjoblom


"Jane" wrote in message
...
Eureka! I found a related posting and did this:
- replaced "BLENDED" w/ D3 where I placed the word BLENDED

=SUMIF($D$12:$D$45,$D$3,$F$12:$F$45)/D4

Thanks all for your ideas!

take good care

"Jane" wrote:

forgive my typo on that - I intended to say that my # of stores was in a
cell
outside of my indicators - sorry for that confusion.

I've tried the suggestions frmo other s with no luck although I am filing
the suggestions for use in other instances if needed so I have learned a
few
more things so happy with that :)

any other thoughts?

thanks, jane

"Ron Rosenfeld" wrote:

On Fri, 17 Aug 2007 10:34:01 -0700, Jane

wrote:

I have used this formula before with success to get an average - (col
A
includes my indicators; col R includes my sales data; col F includes
my # of
stores by which the sales data is divided):
=SUMIF($A$5:$A$500,"R:01",$R$5:$R$500)/$F$107

but I changed it to this for a different spreadsheet:
=SUMIF($A$5:$A$500,"BLENDED",$X$5:$X$500)/A9

Why would it work in the 1st instance but not in the 2nd?

thanks in advance! jane

PRobably because your data is different.

For example, in the second sheet, you are dividing by A9. Is that what
you
really want to do? Is col A still your indicators on this different
sheet?
--ron