I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

Let's see if we can work on this piece by piece. I've answered one question.
Let's get that to work before moving on.

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Let's assume your data is in Rows 2-20 in column I, J, K and L.

For one sheet try this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = "23120"),--(K2:K20="BucketTwo"))

I'm not sure if your values in J are numeric or text. In this example I've
assumed text. If they are numeric, change to (J2:J20 = 23120) without the --
before it.

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

Hi Barb,
Thanx for your help, It didn't work though.
It just returned a value of zero not a sumed dollar figure.
Is there another way to show you the example problem for which i need the
answer?

"Barb Reinhardt" wrote:

Let's see if we can work on this piece by piece. I've answered one question.
Let's get that to work before moving on.

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Let's assume your data is in Rows 2-20 in column I, J, K and L.

For one sheet try this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = "23120"),--(K2:K20="BucketTwo"))

I'm not sure if your values in J are numeric or text. In this example I've
assumed text. If they are numeric, change to (J2:J20 = 23120) without the --
before it.

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

I misunderstood what you wanted. Let's break that equation up a bit.

What do you get for this?

=SUMPRODUCT(--(I2:I20="Op"),(L2:L20))

Or this:
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = 23120),(L2:L20))

Or this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 =
23120),--(K2:K20="BucketTwo"),(L2:L20))

"Barb Reinhardt" wrote:

Let's see if we can work on this piece by piece. I've answered one question.
Let's get that to work before moving on.

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Let's assume your data is in Rows 2-20 in column I, J, K and L.

For one sheet try this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = "23120"),--(K2:K20="BucketTwo"))

I'm not sure if your values in J are numeric or text. In this example I've
assumed text. If they are numeric, change to (J2:J20 = 23120) without the --
before it.

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

Ok,
Your first breakdown worked
The second one produced #VALUE error

"Barb Reinhardt" wrote:

I misunderstood what you wanted. Let's break that equation up a bit.

What do you get for this?

=SUMPRODUCT(--(I2:I20="Op"),(L2:L20))

Or this:
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = 23120),(L2:L20))

Or this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 =
23120),--(K2:K20="BucketTwo"),(L2:L20))

"Barb Reinhardt" wrote:

Let's see if we can work on this piece by piece. I've answered one question.
Let's get that to work before moving on.

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Let's assume your data is in Rows 2-20 in column I, J, K and L.

For one sheet try this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = "23120"),--(K2:K20="BucketTwo"))

I'm not sure if your values in J are numeric or text. In this example I've
assumed text. If they are numeric, change to (J2:J20 = 23120) without the --
before it.

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

OK, try this:

=SUMPRODUCT(--(I2:I20="Op"),--(K2:K20="BucketTwo"),(L2:L20))

If this works, we'll need to figure out what's going on with the value
error. Are the #s in column J TEXT or Numeric.

"I have no idea" wrote:

Ok,
Your first breakdown worked
The second one produced #VALUE error

"Barb Reinhardt" wrote:

I misunderstood what you wanted. Let's break that equation up a bit.

What do you get for this?

=SUMPRODUCT(--(I2:I20="Op"),(L2:L20))

Or this:
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = 23120),(L2:L20))

Or this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 =
23120),--(K2:K20="BucketTwo"),(L2:L20))

"Barb Reinhardt" wrote:

Let's see if we can work on this piece by piece. I've answered one question.
Let's get that to work before moving on.

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Let's assume your data is in Rows 2-20 in column I, J, K and L.

For one sheet try this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = "23120"),--(K2:K20="BucketTwo"))

I'm not sure if your values in J are numeric or text. In this example I've
assumed text. If they are numeric, change to (J2:J20 = 23120) without the --
before it.

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

Operating/Investment Account Code WBS Estimated Allocation
Operating 22071 ZLEBLD230 $2,500.00
Investment 24501 ZLEBLD220 $32,899.00
Operating 22071 ZLEBLD310 $1,470.00
Operating 22071 ZLEBLD310 $174,250.00
Investment 24501 ZLEBLD230 $500.00
Operating 22071 ZLEBLD330 $895,200.00
Operating 22071 ZLEBLD110 $110.00
Operating 21005 ZLEBLD110 $550.00
Investment 24501 ZLEBLD340 $19,825.00
Operating 21005 ZLEBLD220 $110,000.00
Investment 24501 ZLEBLD110 $35,201.00
Operating 21350 ZLEBLD110 $56,893.00
Operating 21047 ZLEBLD220 $7,458.00
Investment 24501 ZLEBLD320 $520.00

I hope the above posted in the right format
Coloums I, (text) J (numeric) and K (alphanumeric) are text format from a
Dropdown list for each row under that coloum. Coloum L is formatted for
Financial data input manually.

Does this help?
Sorry to be a pain. Your help is appreciated.


"Barb Reinhardt" wrote:

OK, try this:

=SUMPRODUCT(--(I2:I20="Op"),--(K2:K20="BucketTwo"),(L2:L20))

If this works, we'll need to figure out what's going on with the value
error. Are the #s in column J TEXT or Numeric.

"I have no idea" wrote:

Ok,
Your first breakdown worked
The second one produced #VALUE error

"Barb Reinhardt" wrote:

I misunderstood what you wanted. Let's break that equation up a bit.

What do you get for this?

=SUMPRODUCT(--(I2:I20="Op"),(L2:L20))

Or this:
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = 23120),(L2:L20))

Or this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 =
23120),--(K2:K20="BucketTwo"),(L2:L20))

"Barb Reinhardt" wrote:

Let's see if we can work on this piece by piece. I've answered one question.
Let's get that to work before moving on.

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Let's assume your data is in Rows 2-20 in column I, J, K and L.

For one sheet try this
=SUMPRODUCT(--(I2:I20="Op"),--(J2:J20 = "23120"),--(K2:K20="BucketTwo"))

I'm not sure if your values in J are numeric or text. In this example I've
assumed text. If they are numeric, change to (J2:J20 = 23120) without the --
before it.

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

Try something for me. Use autofilter to select all the rows that match the
conditions you want and see if you get $550. I'm wondering if the value in
the account # is something like " 12345" instead of "12345".

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

I've checked and no extra space is there.
I autofiltered as you suggested and $550.00 is what came up.

"Barb Reinhardt" wrote:

Try something for me. Use autofilter to select all the rows that match the
conditions you want and see if you get $550. I'm wondering if the value in
the account # is something like " 12345" instead of "12345".

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

Try typing in the value that you are checking for in one of the cells and see
if it works then.

"I have no idea" wrote:

I've checked and no extra space is there.
I autofiltered as you suggested and $550.00 is what came up.

"Barb Reinhardt" wrote:

Try something for me. Use autofilter to select all the rows that match the
conditions you want and see if you get $550. I'm wondering if the value in
the account # is something like " 12345" instead of "12345".

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

Success. I think I have it working.
Now for the next one, can i produce that sumproduct calculation into a
different sheet? In short, can i combine the sumproduct calcultion from two
seperate sheets Say 05-06 and 06-07 into Sheet 3?

Thankyou for your patience.

"Barb Reinhardt" wrote:

Try typing in the value that you are checking for in one of the cells and see
if it works then.

"I have no idea" wrote:

I've checked and no extra space is there.
I autofiltered as you suggested and $550.00 is what came up.

"Barb Reinhardt" wrote:

Try something for me. Use autofilter to select all the rows that match the
conditions you want and see if you get $550. I'm wondering if the value in
the account # is something like " 12345" instead of "12345".

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx

You'll need to replace F2:F20 (for example) with something like this:
Sheet2!F2:F20. Just make sure that each range is the same length.

Good luck!

"I have no idea" wrote:

Success. I think I have it working.
Now for the next one, can i produce that sumproduct calculation into a
different sheet? In short, can i combine the sumproduct calcultion from two
seperate sheets Say 05-06 and 06-07 into Sheet 3?

Thankyou for your patience.

"Barb Reinhardt" wrote:

Try typing in the value that you are checking for in one of the cells and see
if it works then.

"I have no idea" wrote:

I've checked and no extra space is there.
I autofiltered as you suggested and $550.00 is what came up.

"Barb Reinhardt" wrote:

Try something for me. Use autofilter to select all the rows that match the
conditions you want and see if you get $550. I'm wondering if the value in
the account # is something like " 12345" instead of "12345".

"I have no idea" wrote:

I'm sure this is simple nut I have no idea!
(Apologies for incorrect terminoligies)

*Background*
My workbook two worksheets of data.
I want to sum up the values of a range of cells, within a coloum, from both
of these worksheets into a new sheet and the values are based on more than
one criteria.

The info i wanted sumed is in $ format, the criteria in both worksheets are
the same and each worksheet will have a new row added almost daily.

*Example*
Problem 1-
On both worksheets:
Coloum I has a drop down list with two choices "Op" and "Inv"
Coloum J has a drop down list with choices as well "24501", "23120" and
"21005"
Coloum K has a dropdown list with choices as well "BucketOne", "BucketTwo"
and so on.
Coloum L has the value in $ format entered manually.

Question-
How do I sum up in a different sheet all of the values based on "Op" "23120"
"BucketTwo" from both worksheets?

Question-
How do I then seperate that info by the date I choose from say Coloum Z?

Question-
How do I E-mail the person identified in Coloum A that info automatically
when the date in Coloum Z comes around?

Summary
I am the most basic of excel user,so "dumbing down" an answer that even half
solves my problem would be greatlly appreciated.

Thanx