Odd functionality
 

I had submitted a previous question but it was getting confusing and I
believe head ing in the worng direction.

Here is the situation.

I have a document with 2 sheets. On the first sheet I have all of the sales
data. 1 line for each item. The second sheet has totals. I am trying to
calculate the sales on each day but I want to exclude the returns.

What I want to say is....Give me the total sales for xx/xx/xxxx(has its own
cell) but exclude returns(start with "600)

Odd functionality
 

Does this mean you have 3 columns of data:

A column of dates
A column of codes, some of which start with 600
A column of numbers to be summed

You want to sum the numbers for a specific date but exclude those that have
a code that starts with 600. Is this code a number like 612 or is it maybe
an alpanumeric like 600-AA-101?

Biff

"Amber" wrote in message
...
I had submitted a previous question but it was getting confusing and I
believe head ing in the worng direction.

Here is the situation.

I have a document with 2 sheets. On the first sheet I have all of the
sales
data. 1 line for each item. The second sheet has totals. I am trying to
calculate the sales on each day but I want to exclude the returns.

What I want to say is....Give me the total sales for xx/xx/xxxx(has its
own
cell) but exclude returns(start with "600)

Odd functionality
 

The SUMPRODUCT function should work for you.

=SUMPRODUCT(--(Sheet1!A1:A10=A1),--(LEFT(Sheet1!B1:B10,3)<"600"),Sheet1!C1:C10)

This example assumes that your data in Worksheet 1 is stored like this:
A1:A10 is your dates. B1:B10 is your transaction code. C1:C10 is your
dollar amount. You can adjust these to match your actual data.

I've also assumed that the date you are trying to match to has been stored
in cell A1 of worksheet 2. Again, adjust as needed.

HTH,
Elkar


"Amber" wrote:

I had submitted a previous question but it was getting confusing and I
believe head ing in the worng direction.

Here is the situation.

I have a document with 2 sheets. On the first sheet I have all of the sales
data. 1 line for each item. The second sheet has totals. I am trying to
calculate the sales on each day but I want to exclude the returns.

What I want to say is....Give me the total sales for xx/xx/xxxx(has its own
cell) but exclude returns(start with "600)

Odd functionality
 

When I key this function based off of the information in my data, I get #VALUE!

What could the problem be? Any ideas?????

"Elkar" wrote:

The SUMPRODUCT function should work for you.

=SUMPRODUCT(--(Sheet1!A1:A10=A1),--(LEFT(Sheet1!B1:B10,3)<"600"),Sheet1!C1:C10)

This example assumes that your data in Worksheet 1 is stored like this:
A1:A10 is your dates. B1:B10 is your transaction code. C1:C10 is your
dollar amount. You can adjust these to match your actual data.

I've also assumed that the date you are trying to match to has been stored
in cell A1 of worksheet 2. Again, adjust as needed.

HTH,
Elkar


"Amber" wrote:

I had submitted a previous question but it was getting confusing and I
believe head ing in the worng direction.

Here is the situation.

I have a document with 2 sheets. On the first sheet I have all of the sales
data. 1 line for each item. The second sheet has totals. I am trying to
calculate the sales on each day but I want to exclude the returns.

What I want to say is....Give me the total sales for xx/xx/xxxx(has its own
cell) but exclude returns(start with "600)

Odd functionality
 

Yes I do have 3 columns of data.

"T. Valko" wrote:

Does this mean you have 3 columns of data:

A column of dates
A column of codes, some of which start with 600
A column of numbers to be summed

You want to sum the numbers for a specific date but exclude those that have
a code that starts with 600. Is this code a number like 612 or is it maybe
an alpanumeric like 600-AA-101?

Biff

"Amber" wrote in message
...
I had submitted a previous question but it was getting confusing and I
believe head ing in the worng direction.

Here is the situation.

I have a document with 2 sheets. On the first sheet I have all of the
sales
data. 1 line for each item. The second sheet has totals. I am trying to
calculate the sales on each day but I want to exclude the returns.

What I want to say is....Give me the total sales for xx/xx/xxxx(has its
own
cell) but exclude returns(start with "600)