Here is my formula:

=IF(AND(2001DATAB2:B112=€�Gerson, Eileen T.€�,2001 DATAD2:D112=€�General
Fees€�),SUM(2001 DATAG2:G112),€�$ -€œ).

This formula does work but I need to copy it into spreadsheets for several
people and for several categories, and finally for several years.

When I copied the formula and changed the named criteria, ie., Gerson,
Eileen T. and General Fees, it worked fine for the first few people, but then
on about the fifth one, when I copied the information, it copied fine, but
when I changed the criteria, it erred with the following:

#VALUE!

Am I missing something on the copying feature?

Thank you.

It is most likely due to the fact that you are using relative references...

When you copy your formula down
B2:B112 will change to B3:B113 and so on... Is that what you want?

If you want B2:B112 in all cells then use $B$2:$B$112

Other vairation -
B$2:B$112 which will change to C$2:C$112 if copied to the right



"Etg-Para" wrote:

Here is my formula:

=IF(AND(2001DATAB2:B112=€�Gerson, Eileen T.€�,2001 DATAD2:D112=€�General
Fees€�),SUM(2001 DATAG2:G112),€�$ -€œ).

This formula does work but I need to copy it into spreadsheets for several
people and for several categories, and finally for several years.

When I copied the formula and changed the named criteria, ie., Gerson,
Eileen T. and General Fees, it worked fine for the first few people, but then
on about the fifth one, when I copied the information, it copied fine, but
when I changed the criteria, it erred with the following:

#VALUE!

Am I missing something on the copying feature?

Thank you.

No, that did not correct the issue. I am still getting the #VALUE! error.
Any other ideas?

Thank you.

Eileen

"Sheeloo" wrote:

It is most likely due to the fact that you are using relative references...

When you copy your formula down
B2:B112 will change to B3:B113 and so on... Is that what you want?

If you want B2:B112 in all cells then use $B$2:$B$112

Other vairation -
B$2:B$112 which will change to C$2:C$112 if copied to the right



"Etg-Para" wrote:

Here is my formula:

=IF(AND(2001DATAB2:B112=€�Gerson, Eileen T.€�,2001 DATAD2:D112=€�General
Fees€�),SUM(2001 DATAG2:G112),€�$ -€œ).

This formula does work but I need to copy it into spreadsheets for several
people and for several categories, and finally for several years.

When I copied the formula and changed the named criteria, ie., Gerson,
Eileen T. and General Fees, it worked fine for the first few people, but then
on about the fifth one, when I copied the information, it copied fine, but
when I changed the criteria, it erred with the following:

#VALUE!

Am I missing something on the copying feature?

Thank you.

Can you paste the formula from the cell which is giving you trouble?

According to MS;

In Microsoft Excel, when you perform a mathematical operation on cells that
contain text and values, you may receive a #VALUE! error.

CAUSE
Although some functions correctly evaluate cells and ignore text strings, if
you add the cells by using arithmetic operators such as addition (+),
subtraction (-), multiplication (*), or division (/), an error value may
occur.


WORKAROUND
Instead of using an individual mathematical operator, use its equivalent
worksheet function instead:
SUM (adds)
PRODUCT (multiplies)
QUOTIENT (divides)


"Etg-Para" wrote:

No, that did not correct the issue. I am still getting the #VALUE! error.
Any other ideas?

Thank you.

Eileen

"Sheeloo" wrote:

It is most likely due to the fact that you are using relative references...

When you copy your formula down
B2:B112 will change to B3:B113 and so on... Is that what you want?

If you want B2:B112 in all cells then use $B$2:$B$112

Other vairation -
B$2:B$112 which will change to C$2:C$112 if copied to the right



"Etg-Para" wrote:

Here is my formula:

=IF(AND(2001DATAB2:B112=€�Gerson, Eileen T.€�,2001 DATAD2:D112=€�General
Fees€�),SUM(2001 DATAG2:G112),€�$ -€œ).

This formula does work but I need to copy it into spreadsheets for several
people and for several categories, and finally for several years.

When I copied the formula and changed the named criteria, ie., Gerson,
Eileen T. and General Fees, it worked fine for the first few people, but then
on about the fifth one, when I copied the information, it copied fine, but
when I changed the criteria, it erred with the following:

#VALUE!

Am I missing something on the copying feature?

Thank you.