Calculating values from multiplesheets with multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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?
Click to expand...
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.
 
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 said:
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?
Click to expand...
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.
 
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?
 
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?
 
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))
 
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))
 
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.
 
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.
 
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.
 
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.
 
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))
 
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))
 
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.
 
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.
 
Try this. I replaced a * with a ,

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005"),(L2:L44))
 
Try this. I replaced a * with a ,

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZZLEBLD110"),--(J2:J44="21005"),(L2:L44))
 
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))
 
Back
Top