Calculating values from multiplesheets with multiple criteria

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
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

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?
 
G

Guest

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?
 
G

Guest

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))
 
G

Guest

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))
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

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))
 
G

Guest

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))
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

Try this. I replaced a * with a ,

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

Guest

Try this. I replaced a * with a ,

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

Roger Govier

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"sumif" With Multiple Criteria! 3
Excel Already selected values from DropDown list dynamically updating on multiple sheets 10
SUM multiple criteria 1
Summing across multiple worksheets in excel 2007 3
Drop down lists on multiple worksheets 2
Add values based on multiple criteria 4
Collating entries from multiple sheets 3
FREQUENCY with multiple criteria 6

Top