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

The only thing I can think of is that your account codes are text instead of
numeric. Try this

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


"I have no idea" wrote:

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

It shows the following error message

"Microsoft Excel found an error in the formula you entered. Do you want to
accept the correction proposed below?

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005")*(L2:L44))

If i choose yes it returns "0"
If I choose no it does not work at all.

I'm in your capable hands.

"Barb Reinhardt" wrote:

The only thing I can think of is that your account codes are text instead of
numeric. Try this

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


"I have no idea" wrote:

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 this. I replaced a * with a ,

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005"),(L2:L44))


"I have no idea" wrote:

It shows the following error message

"Microsoft Excel found an error in the formula you entered. Do you want to
accept the correction proposed below?

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005")*(L2:L44))

If i choose yes it returns "0"
If I choose no it does not work at all.

I'm in your capable hands.

"Barb Reinhardt" wrote:

The only thing I can think of is that your account codes are text instead of
numeric. Try this

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


"I have no idea" wrote:

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

Hi

Compared with the sample data you posted, there are too many "Z" s in
your criteria.
Also, it looks as though your code numbers are numeric and don't need
the " " around

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZLEBLD110"),--(J2:J44=21005),(L2:L44))


--
Regards

Roger Govier


"I have no idea" wrote in
message ...
It shows the following error message

"Microsoft Excel found an error in the formula you entered. Do you
want to
accept the correction proposed below?

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005")*(L2:L44))

If i choose yes it returns "0"
If I choose no it does not work at all.

I'm in your capable hands.

"Barb Reinhardt" wrote:

The only thing I can think of is that your account codes are text
instead of
numeric. Try this

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


"I have no idea" wrote:

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

So close....
It returned a result of $0.00
It should have shown $550.00
What now :)

"Barb Reinhardt" wrote:

Try this. I replaced a * with a ,

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005"),(L2:L44))


"I have no idea" wrote:

It shows the following error message

"Microsoft Excel found an error in the formula you entered. Do you want to
accept the correction proposed below?

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005")*(L2:L44))

If i choose yes it returns "0"
If I choose no it does not work at all.

I'm in your capable hands.

"Barb Reinhardt" wrote:

The only thing I can think of is that your account codes are text instead of
numeric. Try this

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


"I have no idea" wrote:

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

Hi Roger,
The extra "Z" is from "fat fingers", It's one in the morning after all.
On Barb's advice I removed the " " and it produced the same result



"Roger Govier" wrote:

Hi

Compared with the sample data you posted, there are too many "Z" s in
your criteria.
Also, it looks as though your code numbers are numeric and don't need
the " " around

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZLEBLD110"),--(J2:J44=21005),(L2:L44))


--
Regards

Roger Govier


"I have no idea" wrote in
message ...
It shows the following error message

"Microsoft Excel found an error in the formula you entered. Do you
want to
accept the correction proposed below?

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005")*(L2:L44))

If i choose yes it returns "0"
If I choose no it does not work at all.

I'm in your capable hands.

"Barb Reinhardt" wrote:

The only thing I can think of is that your account codes are text
instead of
numeric. Try this

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


"I have no idea" wrote:

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

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

Hi

I get a result of 550 with the data as posted.
Copy my formula and paste it to your sheet rather typing.

If it still doesn't work, it is your data.
I had to retype your Amounts as they came through as text values from
the posting, but other than that the data was as you sent.

In a spare column type =L2+0 and copy down.
Does it give the values as in column L?

--
Regards

Roger Govier


"I have no idea" wrote in
message ...
Hi Roger,
The extra "Z" is from "fat fingers", It's one in the morning after
all.
On Barb's advice I removed the " " and it produced the same result



"Roger Govier" wrote:

Hi

Compared with the sample data you posted, there are too many "Z" s in
your criteria.
Also, it looks as though your code numbers are numeric and don't need
the " " around

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZLEBLD110"),--(J2:J44=21005),(L2:L44))


--
Regards

Roger Govier


"I have no idea" wrote in
message ...
It shows the following error message

"Microsoft Excel found an error in the formula you entered. Do you
want to
accept the correction proposed below?

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005")*(L2:L44))

If i choose yes it returns "0"
If I choose no it does not work at all.

I'm in your capable hands.

"Barb Reinhardt" wrote:

The only thing I can think of is that your account codes are text
instead of
numeric. Try this

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


"I have no idea" wrote:

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 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 must have been missing or adding something in the formula.

Thanx for that

"Roger Govier" wrote:

Hi

I get a result of 550 with the data as posted.
Copy my formula and paste it to your sheet rather typing.

If it still doesn't work, it is your data.
I had to retype your Amounts as they came through as text values from
the posting, but other than that the data was as you sent.

In a spare column type =L2+0 and copy down.
Does it give the values as in column L?

--
Regards

Roger Govier


"I have no idea" wrote in
message ...
Hi Roger,
The extra "Z" is from "fat fingers", It's one in the morning after
all.
On Barb's advice I removed the " " and it produced the same result



"Roger Govier" wrote:

Hi

Compared with the sample data you posted, there are too many "Z" s in
your criteria.
Also, it looks as though your code numbers are numeric and don't need
the " " around

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZLEBLD110"),--(J2:J44=21005),(L2:L44))


--
Regards

Roger Govier


"I have no idea" wrote in
message ...
It shows the following error message

"Microsoft Excel found an error in the formula you entered. Do you
want to
accept the correction proposed below?

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005")*(L2:L44))

If i choose yes it returns "0"
If I choose no it does not work at all.

I'm in your capable hands.

"Barb Reinhardt" wrote:

The only thing I can think of is that your account codes are text
instead of
numeric. Try this

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


"I have no idea" wrote:

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

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