Need help creating formula to sum tables
 

Hi

I have a massive table which has various departments in column C, services
supplied by the departments in column D and the period invoiced in column L.

When I sort by column C, D and L, I am able to calculate the Gross amount
that has been spent for the period by doing a basic SUM of Column Y.

What I want to achieve is to create some drop down options ( 3 drop down
cells) on a new work sheet that allow me to choose the value for column C,D &
L in 3 fields and for another cell to correctly go back to the previous
worksheet and to correctly sort and calculate the overall total value.

I'm not sure how to do this..... Can someone please give me assistance?

Thanks

Need help creating formula to sum tables
 

If your dropdown options are in A1:C1, and your data is in a worksheet called
Table, try:

=Sumproduct(--(Table!C1:C100=A1), --(Table!D1:D100=B1),
--(Table!L1:L100=C1), Table!Y1:Y100)

change range references to suit.

"Darren Ingram" wrote:

Hi

I have a massive table which has various departments in column C, services
supplied by the departments in column D and the period invoiced in column L.

When I sort by column C, D and L, I am able to calculate the Gross amount
that has been spent for the period by doing a basic SUM of Column Y.

What I want to achieve is to create some drop down options ( 3 drop down
cells) on a new work sheet that allow me to choose the value for column C,D &
L in 3 fields and for another cell to correctly go back to the previous
worksheet and to correctly sort and calculate the overall total value.

I'm not sure how to do this..... Can someone please give me assistance?

Thanks

Need help creating formula to sum tables
 

Wow. It does work, however wyhy do I need "--" before the brackets, as the
original formula doesn't have such thing. Just as an explanation, Darren post
it this request on my behalf, which I am really grateful for.

Can I also use this formula to add other conditionals such payment received
or yet to receive just by adding extra range with additional criteria??

Thanks Piotr

"JMB" wrote:

If your dropdown options are in A1:C1, and your data is in a worksheet called
Table, try:

=Sumproduct(--(Table!C1:C100=A1), --(Table!D1:D100=B1),
--(Table!L1:L100=C1), Table!Y1:Y100)

change range references to suit.

"Darren Ingram" wrote:

Hi

I have a massive table which has various departments in column C, services
supplied by the departments in column D and the period invoiced in column L.

When I sort by column C, D and L, I am able to calculate the Gross amount
that has been spent for the period by doing a basic SUM of Column Y.

What I want to achieve is to create some drop down options ( 3 drop down
cells) on a new work sheet that allow me to choose the value for column C,D &
L in 3 fields and for another cell to correctly go back to the previous
worksheet and to correctly sort and calculate the overall total value.

I'm not sure how to do this..... Can someone please give me assistance?

Thanks

Need help creating formula to sum tables
 

the -- converts a true/false value to its underlying numeric value ( 1 for
true, 0 for false). It also converts "text" numbers to numeric values that
can be used in arithmetic operations.

Yes, you can add additional criteria. Be sure the ranges are the same size
as those already in the formula.

One of the best write-ups on sumproduct is on Bob phillips site
Xldynamic.com. At the bottom left of the screen you should see a linke
regarding Multiple Condition Tests. It's well worth the time to read.

Sorry for delayed response - I'm out of town and have limited computer access.
"Piotr (Peter)" wrote:

Wow. It does work, however wyhy do I need "--" before the brackets, as the
original formula doesn't have such thing. Just as an explanation, Darren post
it this request on my behalf, which I am really grateful for.

Can I also use this formula to add other conditionals such payment received
or yet to receive just by adding extra range with additional criteria??

Thanks Piotr

"JMB" wrote:

If your dropdown options are in A1:C1, and your data is in a worksheet called
Table, try:

=Sumproduct(--(Table!C1:C100=A1), --(Table!D1:D100=B1),
--(Table!L1:L100=C1), Table!Y1:Y100)

change range references to suit.

"Darren Ingram" wrote:

Hi

I have a massive table which has various departments in column C, services
supplied by the departments in column D and the period invoiced in column L.

When I sort by column C, D and L, I am able to calculate the Gross amount
that has been spent for the period by doing a basic SUM of Column Y.

What I want to achieve is to create some drop down options ( 3 drop down
cells) on a new work sheet that allow me to choose the value for column C,D &
L in 3 fields and for another cell to correctly go back to the previous
worksheet and to correctly sort and calculate the overall total value.

I'm not sure how to do this..... Can someone please give me assistance?

Thanks

Need help creating formula to sum tables
 

Thank you JMB for your assistance and don't worry about the delay. You have
helped anyway as I achived what I needed. I will certainly read this website
as there is a long way before I will be able to do more complicated things on
my own. Thanks again!!

"JMB" wrote:

the -- converts a true/false value to its underlying numeric value ( 1 for
true, 0 for false). It also converts "text" numbers to numeric values that
can be used in arithmetic operations.

Yes, you can add additional criteria. Be sure the ranges are the same size
as those already in the formula.

One of the best write-ups on sumproduct is on Bob phillips site
Xldynamic.com. At the bottom left of the screen you should see a linke
regarding Multiple Condition Tests. It's well worth the time to read.

Sorry for delayed response - I'm out of town and have limited computer access.
"Piotr (Peter)" wrote:

Wow. It does work, however wyhy do I need "--" before the brackets, as the
original formula doesn't have such thing. Just as an explanation, Darren post
it this request on my behalf, which I am really grateful for.

Can I also use this formula to add other conditionals such payment received
or yet to receive just by adding extra range with additional criteria??

Thanks Piotr

"JMB" wrote:

If your dropdown options are in A1:C1, and your data is in a worksheet called
Table, try:

=Sumproduct(--(Table!C1:C100=A1), --(Table!D1:D100=B1),
--(Table!L1:L100=C1), Table!Y1:Y100)

change range references to suit.

"Darren Ingram" wrote:

Hi

I have a massive table which has various departments in column C, services
supplied by the departments in column D and the period invoiced in column L.

When I sort by column C, D and L, I am able to calculate the Gross amount
that has been spent for the period by doing a basic SUM of Column Y.

What I want to achieve is to create some drop down options ( 3 drop down
cells) on a new work sheet that allow me to choose the value for column C,D &
L in 3 fields and for another cell to correctly go back to the previous
worksheet and to correctly sort and calculate the overall total value.

I'm not sure how to do this..... Can someone please give me assistance?

Thanks