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